1. 题目需求
从订单明细表(order_detail)中查询出所有购买过商品1和商品2,但是没有购买过商品3的用户,
期望结果如下:
2. 需要用到的表:
订单信息表:order_info
订单明细表:order_detail
3. 查询sql
SELECT
user_id
from
(
SELECT
oi.user_id,
od.sku_id
from
order_detail od
join order_info oi on od.order_id = oi.order_id
group by
oi.user_id,
od.sku_id
) t
group by
user_id
having
sum(if (sku_id = 3, -3, if (sku_id in (1, 2), 1, 0))) = 2