SQL查询求助：除sku_num外其余列值相同时合并sku_num为逗号分隔值

没问题，我来帮你搞定这个SQL需求！你要的是把除sku_num外其他列都相同的行里的sku_num合并成逗号分隔的字符串，核心思路是分组聚合——先把所有非sku_num列值相同的行归为一组，再用字符串聚合函数把每组的sku_num拼接起来。下面根据不同主流数据库给你具体的实现代码：

分数据库具体实现 #

1. MySQL / MariaDB #

用GROUP_CONCAT()函数，默认就是逗号分隔，非常方便：

SELECT 
  chassis_option.chassis_id AS `Chassis ID`,
  chassis_option.module_id AS `Module ID`,
  chassis_option.internal_name AS `Option Internal Name`,
  -- 这里要把你需要保留的其他所有列都列出来
  GROUP_CONCAT(chassis_option.sku_num SEPARATOR ',') AS `Combined SKU Numbers`
FROM chassis_option
-- 如果有JOIN关联或者WHERE过滤条件，写在这里
GROUP BY 
  chassis_option.chassis_id,
  chassis_option.module_id,
  chassis_option.internal_name
  -- 注意：GROUP BY必须包含SELECT里所有非聚合函数的列，要和上面一一对应

2. SQL Server 2017+/Azure SQL #

使用微软的STRING_AGG()函数：

SELECT 
  chassis_option.chassis_id AS [Chassis ID],
  chassis_option.module_id AS [Module ID],
  chassis_option.internal_name AS [Option Internal Name],
  -- 其他需要保留的列
  STRING_AGG(chassis_option.sku_num, ',') AS [Combined SKU Numbers]
FROM chassis_option
-- JOIN/WHERE条件写在这里
GROUP BY 
  chassis_option.chassis_id,
  chassis_option.module_id,
  chassis_option.internal_name
  -- 同样，GROUP BY要包含所有非聚合列

3. PostgreSQL #

和SQL Server类似，用STRING_AGG()：

SELECT 
  chassis_option.chassis_id AS "Chassis ID",
  chassis_option.module_id AS "Module ID",
  chassis_option.internal_name AS "Option Internal Name",
  -- 其他需要保留的列
  STRING_AGG(chassis_option.sku_num, ',') AS "Combined SKU Numbers"
FROM chassis_option
-- JOIN/WHERE条件
GROUP BY 
  chassis_option.chassis_id,
  chassis_option.module_id,
  chassis_option.internal_name

4. Oracle 11g+ #

使用LISTAGG()函数，需要指定排序规则：

SELECT 
  chassis_option.chassis_id AS "Chassis ID",
  chassis_option.module_id AS "Module ID",
  chassis_option.internal_name AS "Option Internal Name",
  -- 其他需要保留的列
  LISTAGG(chassis_option.sku_num, ',') WITHIN GROUP (ORDER BY chassis_option.sku_num) AS "Combined SKU Numbers"
FROM chassis_option
-- JOIN/WHERE条件
GROUP BY 
  chassis_option.chassis_id,
  chassis_option.module_id,
  chassis_option.internal_name

几个实用小技巧 #

• 如果分组里有重复的sku_num，想去掉重复值，可以在聚合函数里加DISTINCT，比如GROUP_CONCAT(DISTINCT sku_num)或者STRING_AGG(DISTINCT sku_num, ',')
• 要是想让拼接后的sku_num按顺序排列，MySQL可以写GROUP_CONCAT(sku_num ORDER BY sku_num)，Oracle已经在LISTAGG里指定了ORDER BY，SQL Server和PostgreSQL可以用STRING_AGG(sku_num, ',' ORDER BY sku_num)

内容的提问来源于stack exchange，提问作者Frank D