同事最近做项目遇到了一个sql语句的问题,找我一起讨论解决,需要查询的信息虽然比较复杂,但还是可以简化成一个问题,表结构:
id type
1 a
1 b
1 c
1 d
2 a
3 g
4 b
sql查找既有a又有b的id
结果
1
上述SQL语句:
SELECT
a.id
FROM a
WHERE a.type IN (‘a’,‘b’)
GROUP BY a.id
HAVING COUNT(a.id) = 2
我们的需求最终SQL语句:
SELECT a.user_id
FROM (
SELECT
d.user_id ,
d.v_id
FROM dxw_voucher_grant_base
d
LEFT JOIN dxw_voucher_base
e ON e.id = d.v_id
GROUP BY d.user_id , d.v_id
ORDER BY d.user_id
) a
WHERE a.v_id IN (SELECT id FROM dxw_voucher_base WHERE is_ovip = 1)
GROUP BY a.user_id
HAVING COUNT(a.user_id) = (SELECT COUNT(1) FROM dxw_voucher_base WHERE is_ovip = 1)
多做总结。