大数据SQL题18 购买过商品1和商品2但是没有购买商品3的顾客

最新推荐文章于 2024-07-21 16:17:25 发布

吃派小星

最新推荐文章于 2024-07-21 16:17:25 发布

阅读量441

点赞数 3

分类专栏：大数据SQL题文章标签：大数据 sql 数据库

本文链接：https://blog.csdn.net/qq_35237689/article/details/137969578

版权

大数据SQL题专栏收录该内容

50 篇文章 0 订阅

订阅专栏

文章介绍了如何使用SQL查询从订单明细表中筛选出购买了商品1和2但未购买商品3的用户，提供了三种不同的解题思路：集合差、外连接以及统计值判断。

摘要由CSDN通过智能技术生成

原题链接：http://practice.atguigu.cn/#/question/18/desc?qType=SQL

题目需求

从订单明细表(order_detail)中查询出所有购买过商品1和商品2，但是没有购买过商品3的用户，

期望结果如下：

user_id
103
105

需要用到的表：

订单信息表：order_info

order_id (订单id)	user_id (用户id)	create_date (下单日期)	total_amount (订单金额)
1	101	2021-09-30	29000.00
10	103	2020-10-02	28000.00

订单明细表：order_detail

order_detail_id(订单明细id)	order_id(订单id)	sku_id(商品id)	create_date(下单日期)	price(商品单价)	sku_num(商品件数)
1	1	1	2021-09-30	2000.00	2
2	1	3	2021-09-30	5000.00	5
22	10	4	2020-10-02	6000.00	1
23	10	5	2020-10-02	500.00	24
24	10	6	2020-10-02	2000.00	5

解题思路

使用except做集合差

SELECT  user_id
FROM order_detail
WHERE sku_id IN (1, 2)
GROUP BY  user_id 
except
SELECT  user_id
FROM order_detail
WHERE sku_id = 3
GROUP BY  user_id

外连接

SELECT  t1.user_id
FROM
(
	SELECT  oi.user_id,
	        COUNT(DISTINCT sku_id) AS sku_cnt
	FROM order_detail od
	JOIN order_info oi
	ON od.order_id = oi.order_id
	WHERE od.sku_id IN (1, 2)
	GROUP BY  oi.user_id
) t1
LEFT JOIN
(
	SELECT  oi.user_id
	FROM order_detail od
	JOIN order_info oi
	ON od.order_id = oi.order_id
	WHERE od.sku_id = 3
	GROUP BY  oi.user_id
) t2
ON t1.user_id = t2.user_id
WHERE t2.user_id IS NULL
AND t1.sku_cnt = 2

设计统计值判断

SELECT  t1.user_id
FROM
(
	SELECT  oi.user_id,
	        COUNT(DISTINCT IF(sku_id IN (1,2),sku_id,NULL)) AS flag_1,
	        COUNT(DISTINCT IF(sku_id = 3,sku_id,NULL))      AS flag_2
	FROM order_detail od
	JOIN order_info oi
	ON od.order_id = oi.order_id
	WHERE od.sku_id IN (1, 2, 3)
	GROUP BY  oi.user_id
) t1
WHERE flag_1 = 2 AND flag_2 = 0