表1:用户字段:id、电子邮件、first_name
表2:资源字段:user_id、product_id
我想从用户表中选择电子邮件,first_name,其中用户有product_id 22,但没有产品id 1、3或35
获取此列表的SQL是什么?
select email, first_name
from users
where id in (select user_id from resources where product_id=22)
and id not in (select user_id from resources where product_id IN (1,3,35))
SELECT u.email,
u.first_name
FROM users u
JOIN resources r
ON u.id = r.user_id
WHERE product_id IN ( 1, 3, 22, 35 )
GROUP BY u.id,
u.email,
u.first_name
HAVING COUNT(DISTINCT product_id = 1)
AND MAX(product_id) = 22