本来今天这道题是想放到24号发的，因为昨晚这道题当时没想到怎么做，后来24号的也没发...

题干

1045. 买下所有产品的客户 Customer 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| customer_id | int     |
| product_key | int     |
+-------------+---------+
该表可能包含重复的行。
customer_id 不为 NULL。
product_key 是 Product 表的外键(reference 列)。

Product 表：

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_key | int     |
+-------------+---------+
product_key 是这张表的主键（具有唯一值的列）。

编写解决方案，报告 Customer 表中购买了 Product 表中所有产品的客户的 id。

返回结果表 无顺序要求 。

返回结果格式如下所示。

示例 1：

输入：
Customer 表：
+-------------+-------------+
| customer_id | product_key |
+-------------+-------------+
| 1           | 5           |
| 2           | 6           |
| 3           | 5           |
| 3           | 6           |
| 1           | 6           |
+-------------+-------------+
Product 表：
+-------------+
| product_key |
+-------------+
| 5           |
| 6           |
+-------------+
输出：
+-------------+
| customer_id |
+-------------+
| 1           |
| 3           |
+-------------+
解释：
购买了所有产品（5 和 6）的客户的 id 是 1 和 3 。

测试数据


drop table if exists Product;
create table Product(product_key int);
insert into Product(product_key) values(3),(1),(2);

drop table if exists Customer;
create table Customer(customer_id int,product_key int);
insert into Customer(customer_id,product_key) values(1,5),(2,6),(3,5),(3,6),(1,6);

解析

昨晚咋一看这道题，觉得应该是要对比两个SQL的结果，可能用到数组交集做判断，但是估计没那么容易写，就睡觉去了。今天一看，想了一下，可以将这个数组的交集转换为判断字符串全等的操作，难度一下子就降下来了。

对每个用户的所有购买的商品ID去重后分组排序（这个顺序切记要与下面的排序一致），然后再将每个用户购买的商品id拼接成字符串。

要将多行转单列，这里用的是group_concat，需要注意一般是跟group by一起用，如果是单列的多行转单行则不需要group by。 group_concat的语法：

GROUP_CONCAT([DISTINCT] expr [,expr ...]  
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]  
[SEPARATOR str_val])

SEPARATOR后面要跟着分隔符。如果是要先排序后再group_concat，要在函数内用order by。

如果是先用子查询排序后再group_concat，子查询的排序是不生效的，具体原因就不清楚了。（别问为啥要子查询排序，问就是不看语法先写的group_concat(id SEPARATOR ',' order by id asc)发现报错，以为group_concat函数内不能跟order by，没想到是我顺序写错了，论先看文档的重要性QAQ）

然后是关于列换行、行转列的相关内容，GROUP_CONCAT()函数相关内容可以看下下面几个博客

www.cnblogs.com/ministep/p/… juejin.cn/post/702993… blog.csdn.net/william_n/a…

下面直接看SQL吧，文字功底太差了。

作答

首先对用户购买的商品的表进行去重后分组排序

-- rn列只是辅助排序用，因为要分组排序
select 
  customer_id, product_key, 
  row_number() over(partition by customer_id order by product_key asc) as rn 
from 
  (select 
      distinct customer_id, product_key 
    from Customer
  ) as t1
;

然后将分组后的结果按照每个用户分组购买的商品进行拼接

select customer_id, group_concat(product_key separator ',') as products
    from
        (select 
            customer_id, product_key,
            row_number() over(partition by customer_id order by product_key asc) as rn 
        from (select distinct customer_id, product_key from Customer) as t1
        ) as t2
    group by customer_id

将商品表也进行排序后拼接起来。切记这个排序顺序要与上面的顺序一致，否则无法正确做等值判断。

select group_concat(product_key order by product_key asc separator ',') as products from Product;

最后将上面两个临时表做内连接即可

select t3.customer_id from
    (select customer_id, group_concat(product_key separator ',') as products
    from
        (select 
            customer_id, product_key,
            row_number() over(partition by customer_id order by product_key asc) as rn 
        from (select distinct customer_id, product_key from Customer) as t1
        ) as t2
    group by customer_id) as t3
inner join (select group_concat(product_key order by product_key asc separator ',') as products from Product) as t4
on t3.products=t4.products
;

每日SQL一练#20231025

题干

测试数据

解析

作答