如何在MySQL中对指定列的拼接结果实现去重

你的查询当前使用CONCAT_WS(',', D, E, F, G)拼接多列，但结果包含重复值。以下是针对不同数据库的解决方案，可实现去除重复值后拼接为(唯一值列表)的格式：

MySQL 解决方案 #

版本8.0+（支持JSON_TABLE） #

利用JSON函数将列转为行数据，去重后重新拼接：

SELECT 
  `Column A`, `Column B`, `Column C`, `Column D`, `Column E`, `Column F`, `Column G`,
  CONCAT('(', GROUP_CONCAT(DISTINCT val ORDER BY val SEPARATOR ','), ')') AS unique_concat
FROM your_table
JOIN JSON_TABLE(
  CONCAT('[', `Column D`, ',', `Column E`, ',', `Column F`, ',', `Column G`, ']'),
  '$[*]' COLUMNS(val INT PATH '$')
) AS jt
GROUP BY `Column A`, `Column B`, `Column C`, `Column D`, `Column E`, `Column F`, `Column G`;

版本5.7及以下（无JSON_TABLE） #

通过UNION ALL将多列转为行，再分组去重拼接：

SELECT 
  t.`Column A`, t.`Column B`, t.`Column C`, t.`Column D`, t.`Column E`, t.`Column F`, t.`Column G`,
  CONCAT('(', GROUP_CONCAT(DISTINCT v.val ORDER BY v.val SEPARATOR ','), ')') AS unique_concat
FROM your_table t
JOIN (
  SELECT `Column A`, `Column B`, `Column C`, `Column D` AS val FROM your_table
  UNION ALL
  SELECT `Column A`, `Column B`, `Column C`, `Column E` AS val FROM your_table
  UNION ALL
  SELECT `Column A`, `Column B`, `Column C`, `Column F` AS val FROM your_table
  UNION ALL
  SELECT `Column A`, `Column B`, `Column C`, `Column G` AS val FROM your_table
) v USING (`Column A`, `Column B`, `Column C`)
GROUP BY t.`Column A`, t.`Column B`, t.`Column C`, t.`Column D`, t.`Column E`, t.`Column F`, t.`Column G`;

PostgreSQL 解决方案 #

使用UNNEST将数组转为行，结合STRING_AGG去重拼接：

SELECT 
  "Column A", "Column B", "Column C", "Column D", "Column E", "Column F", "Column G",
  CONCAT('(', STRING_AGG(DISTINCT val::TEXT, ',' ORDER BY val), ')') AS unique_concat
FROM your_table,
UNNEST(ARRAY["Column D", "Column E", "Column F", "Column G"]) AS val
GROUP BY "Column A", "Column B", "Column C", "Column D", "Column E", "Column F", "Column G";

SQL Server 解决方案 #

版本2016+（支持STRING_SPLIT） #

拆分拼接后的字符串为行，去重后重新聚合：

SELECT 
  [Column A], [Column B], [Column C], [Column D], [Column E], [Column F], [Column G],
  CONCAT('(', STRING_AGG(DISTINCT val, ','), ')') AS unique_concat
FROM your_table
CROSS APPLY (
  SELECT value AS val FROM STRING_SPLIT(CONCAT_WS(',', [Column D], [Column E], [Column F], [Column G]), ',')
) AS split_vals
GROUP BY [Column A], [Column B], [Column C], [Column D], [Column E], [Column F], [Column G];

通用版本（使用UNPIVOT） #

通过UNPIVOT将列转为行，再分组聚合：

SELECT 
  [Column A], [Column B], [Column C], [Column D], [Column E], [Column F], [Column G],
  CONCAT('(', STRING_AGG(DISTINCT val, ','), ')') AS unique_concat
FROM your_table
UNPIVOT (
  val FOR col IN ([Column D], [Column E], [Column F], [Column G])
) AS unpivoted
GROUP BY [Column A], [Column B], [Column C], [Column D], [Column E], [Column F], [Column G];

执行以上查询后，第一条记录的unique_concat列会返回(14,15,16)，第二条返回(14,15,16,17)，完全符合你的需求。

内容的提问来源于stack exchange，提问作者m mardani