我有如下数据：
Category | Type | Rank
Milk 1 1
Milk 2 2
Milk 3 3
Chocolate 1 2
Candy 1 1
使用平面 SQL 查询实现以下输出的任何想法：
Category
Milk
Query must satisfy the below conditions:
1. Only Type 1 and Rank 1 will be selected.
2. Only Category that has Type 1 and Type 2 will be selected.
在上面的样本数据中，只有满足上述条件的 Milk。
我的查询如下。但这是不正确的，因为它也会返回 Candy。
SELECT DISTINCT Category
FROM table
WHERE Type = 1 AND rank = 1
提前致谢！
您可以使用聚合：
select category
from t
group by category
having sum(case when type = 1 and rank = 1 then 1 else 0 end) > 0 and
sum(case when type = 2 then 1 else 0 end) > 0;
假设没有重复，这可以简化为：
select category
from t
where (type = 1 and rank = 1) or type = 2
group by category
having count(distinct type) = 2;
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句