示例数据:
table表:有如下2列
ID CODE
A C1
A C2
A C3
A C4
A C5
B C1
B C2
B C3
B C4
B C5
B C6
B C7
C C1
C C2
C C3
C C4
针对每个ID,检查code列中是否同时含有C1 C3 C4 C5 四个值,
table表:有如下2列
ID CODE
A C1
A C2
A C3
A C4
A C5
B C1
B C2
B C3
B C4
B C5
B C6
B C7
C C1
C C2
C C3
C C4
针对每个ID,检查code列中是否同时含有C1 C3 C4 C5 四个值,
返回 ID和CODE 即可
解答:
先过滤在聚合 select id from t where code in('C1', 'C3', 'C4', 'C5') group by id having count(distinct code) = 4 |