如何用SQL检索同时属于Eng和IT子分类的库存物品？

现有库存物品分为Engineering（简称Eng）和Information Technology（简称IT）两大主类，下分多个子分类（例如IT的IT059、IT059L，Eng的Eng200、Eng209等）。需从Item和Department两张数据库表中，筛选出同时属于Eng和IT子分类的物品item_id。

Item表 #

item_id,    item_name,      date_purchased
101,        A101,           2012-01-01
101,        A101,           2012-01-02
101,        A101,           2012-01-03
101,        A101,           2012-01-04
150,        B150,           2012-01-05
202,        C202,           2012-01-06
202,        C202,           2012-01-07
202,        C202,           2012-01-08
221,        D221,           2012-01-09
303,        E303,           2012-01-10
303,        E303,           2012-01-11
303,        E303,           2012-01-12
351,        F351,           2012-01-13
404,        G404,           2012-01-14
404,        G404,           2012-01-15
414,        H414,           2012-01-16
505,        I505,           2012-01-17
505,        I505,           2012-01-18
516,        J516,           2012-01-19
606,        K606,           2012-01-20
606,        K606,           2012-01-21
606,        K606,           2012-01-22
707,        L707,           2012-01-23
707,        L707,           2012-01-24
707,        L707,           2012-01-25
707,        L707,           2012-01-26
707,        L707,           2012-01-27
707,        L707,           2012-01-28
707,        L707,           2012-01-29
808,        M808,           2012-01-30
808,        M808,           2012-01-31
808,        M808,           2012-02-01
808,        M808,           2012-02-02
909,        N909,           2012-02-03
909,        N909,           2012-02-04
909,        N909,           2012-02-05
909,        N909,           2012-02-05
909,        N909,           2012-02-07
950,        O950,           2012-02-08
950,        O950,           2012-02-09
970,        P970,           2012-02-10
970,        P970,           2012-02-11
970,        P970,           2012-02-12
970,        P970,           2012-02-13
970,        P970,           2012-02-14
999,        Q999,           2012-02-15

Department表 #

item_id,    department_id
101,        Eng200
101,        IT059
101,        IT059L
101,        IT069
150,        Eng200
202,        Eng209
202,        IT059
202,        IT060L
221,        IT060L
303,        Eng209
303,        IT06.9
303,        IT069
351,        Eng20-
404,        Eng209
404,        IT064
414,        Eng20.1
505,        Eng209
505,        IT060
516,        IT05..
606,        Eng209
606,        IT060M
606,        IT069
707,        Eng209
707,        IT058H
707,        IT059
707,        IT06.0
707,        IT06.9
707,        IT060
707,        IT069
808,        Eng209
808,        IT059
808,        IT059M
808,        IT069
909,        Eng209
909,        IT059
909,        IT060
909,        IT060M
909,        IT069G
950,        Eng20-
950,        IT069
970,        Eng20-
970,        IT058L
970,        IT059
970,        IT059L
970,        IT069
999,        Eng20?

简单场景的查询（仅两个主类） #

如果数据库中只有Eng和IT两个顶层department_id，可使用以下查询：

select max(i.item_id)
from item i
join department d
on i.item_id = d.item_id
group by i.item_id
having count(distinct d.department_id) > 1

实际场景的解决方案 #

但实际存在多个子分类，需要先判断物品是否同时归属Eng和IT主类。可以通过前缀匹配将子分类映射到主类，再统计每个物品的主类覆盖情况：

方法一：使用SUM判断两类都存在

SELECT DISTINCT i.item_id
FROM Item i
JOIN Department d ON i.item_id = d.item_id
GROUP BY i.item_id
HAVING 
    -- 至少存在一个Eng类的子分类
    SUM(CASE WHEN d.department_id LIKE 'Eng%' THEN 1 ELSE 0 END) > 0
    -- 至少存在一个IT类的子分类
    AND SUM(CASE WHEN d.department_id LIKE 'IT%' THEN 1 ELSE 0 END) > 0;

方法二：使用COUNT(DISTINCT)统计主类数量

SELECT i.item_id
FROM Item i
JOIN Department d ON i.item_id = d.item_id
GROUP BY i.item_id
HAVING 
    COUNT(DISTINCT 
        CASE 
            WHEN d.department_id LIKE 'Eng%' THEN 'Eng'
            WHEN d.department_id LIKE 'IT%' THEN 'IT'
        END) = 2;

期望输出 #

item_id
101
202
303
404
505
606
707
808
909
950
970

内容的提问来源于stack exchange，提问作者GKC