如何查询所有子项value均为1的ITEM？SQL查询求助

表结构 #

表1 (table1) #

表2 (table2) #

表3 (table3) #

需求 #

查询所有对应子项SUBITEM的value均为1的ITEM。比如XXXX不能被列出，因为它的子项BB对应的value是0。

原SQL问题 #

原SQL会返回所有存在至少一个value为1的子项的ITEM，不符合需求：

select distinct         
(table1.item)  
from table1,        
table2,        
table3 
where table1.id = table2.id        
and table2.id_2 = table3.id_2        
and table3.value = 1 
order by table1.item

修正方案 #

方案1：用NOT EXISTS排除存在value=0的ITEM #

思路：直接找出所有不存在对应子项value为0的ITEM，剩下的自然是子项全为1的。

SELECT t1.ITEM
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2 t2
    JOIN table3 t3 ON t2.ID_2 = t3.ID_2
    WHERE t2.ID = t1.ID
      AND t3.value = 0
)
ORDER BY t1.ITEM;

方案2：GROUP BY + HAVING筛选 #

思路：按ITEM分组后，检查该组下所有子项的最小value是否为1（只要有一个子项是0，最小值就会变成0）；或者统计value=0的子项数量，数量为0就符合要求。

SELECT t1.ITEM
FROM table1 t1
JOIN table2 t2 ON t1.ID = t2.ID
JOIN table3 t3 ON t2.ID_2 = t3.ID_2
GROUP BY t1.ID, t1.ITEM
HAVING MIN(t3.value) = 1
ORDER BY t1.ITEM;

或者用COUNT逻辑：

SELECT t1.ITEM
FROM table1 t1
JOIN table2 t2 ON t1.ID = t2.ID
JOIN table3 t3 ON t2.ID_2 = t3.ID_2
GROUP BY t1.ID, t1.ITEM
HAVING COUNT(CASE WHEN t3.value = 0 THEN 1 END) = 0
ORDER BY t1.ITEM;

方案3：LEFT JOIN筛选无匹配的value=0项 #

思路：把table1和“子项value=0”的关联结果做左连接，筛选出没有匹配到value=0子项的ITEM。

SELECT t1.ITEM
FROM table1 t1
LEFT JOIN table2 t2 ON t1.ID = t2.ID
LEFT JOIN table3 t3 ON t2.ID_2 = t3.ID_2 AND t3.value = 0
WHERE t3.ID_2 IS NULL
GROUP BY t1.ID, t1.ITEM
ORDER BY t1.ITEM;

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