购买了产品 A 和产品 B 却没有购买产品 C 的顾客
题目分析:要求找出选了AB两个产品,但是没有选C产品的顾客。很自然想到的一个方法是我直接group by后比较下不就行了。那么有group by后集合的函数吗?有,真有,group_concat
先按照customer_id集合下数据
select customer_id,group_concat(product_name) p_name from Orders group by customer_id
接着筛选出符合条件的id
select customer_id from (
select customer_id,group_concat(product_name) p_name from Orders group by customer_id) F1 where p_name LIKE "%A%" and p_name LIKE "%B%" and p_name not LIKE "%C%") F2
最后inner join得出结果
select C.customer_id,C.customer_name from (
select customer_id from (
select customer_id,group_concat(product_name) p_name from Orders group by customer_id) F1 where p_name LIKE "%A%" and p_name LIKE "%B%" and p_name not LIKE "%C%") F2 inner join Customers C on F2.customer_id = C.customer_id order by C.customer_id;
提交结果如下