product_id | category
---------------------
1 | A
1 | B
2 | A
3 | B
我想知道哪些唯一 ID 屬于 A 類但不屬于 B 類。在上面的示例中，只有id: 2一個有效的結果
我想出了這個不能按預期作業：
SELECT id
FROM table
WHERE category = 'A' AND category <> 'B'
GROUP BY id
一種有效的方法是使用子查詢
SELECT id
FROM table
WHERE category = 'A'
AND id NOT IN (SELECT id
FROM table
WHERE category = 'B')
是否可以通過單個查詢來做到這一點？
uj5u.com熱心網友回復：
使用自聯接：
SELECT t1.id
FROM table AS t1
LEFT JOIN table AS t2 ON t1.id = t2.id AND t2.category = 'B'
WHERE t1.category = 'A' and t2.id IS NULL
另一種方法是分組：
SELECT id
FROM table
WHERE category IN ('A', 'B')
GROUP BY id
HAVING MAX(category) = 'A'
uj5u.com熱心網友回復：
使用聚合和HAVING子句中的條件：
SELECT id
FROM tablename
GROUP BY id
HAVING SUM(category = 'A') > 0
AND SUM(category = 'B') = 0;
或者：
SELECT id
FROM tablename
WHERE category IN ('A', 'B')
GROUP BY id
HAVING SUM(category = 'B') = 0;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/467576.html