如何基于列数据合并SQL表中重复年份与地点的行？

先给你修正下原WHERE语句的逻辑漏洞：OR的优先级比AND低，原写法可能会跑出不符合预期的结果，建议简化成更清晰的版本：

WHERE Type = 'Type' 
  AND Location IN ('CountryA', 'CountryB')

接下来要合并同年同地点同Type的行、把Count用加号连起来，不同数据库的字符串聚合函数不一样，给你分情况列出来：

MySQL/MariaDB #

用GROUP_CONCAT函数，指定分隔符为+就行：

SELECT 
  Year,
  Location,
  Rand, -- 假设同组里Rand值都一样，要是不一样的话可以用MIN(Rand)或MAX(Rand)取一个
  Rand AS Rand2, -- 对应第二个Rand列，逻辑同上
  Type,
  GROUP_CONCAT(Count SEPARATOR '+') AS Combined_Count
FROM BOOK1
WHERE Type = 'Type' 
  AND Location IN ('CountryA', 'CountryB')
GROUP BY Year, Location, Rand, Rand2, Type
ORDER BY Location ASC, Year ASC;

SQL Server/Azure SQL #

用STRING_AGG函数，还能指定Count的拼接顺序：

SELECT 
  Year,
  Location,
  Rand,
  Rand AS Rand2,
  Type,
  STRING_AGG(Count, '+') WITHIN GROUP (ORDER BY Count) AS Combined_Count
FROM BOOK1
WHERE Type = 'Type' 
  AND Location IN ('CountryA', 'CountryB')
GROUP BY Year, Location, Rand, Rand2, Type
ORDER BY Location ASC, Year ASC;

PostgreSQL #

同样用STRING_AGG，不过要把Count转成文本类型：

SELECT 
  Year,
  Location,
  Rand,
  Rand AS Rand2,
  Type,
  STRING_AGG(Count::TEXT, '+') AS Combined_Count
FROM BOOK1
WHERE Type = 'Type' 
  AND Location IN ('CountryA', 'CountryB')
GROUP BY Year, Location, Rand, Rand2, Type
ORDER BY Location ASC, Year ASC;

Oracle #

用LISTAGG函数：

SELECT 
  Year,
  Location,
  Rand,
  Rand AS Rand2,
  Type,
  LISTAGG(Count, '+') WITHIN GROUP (ORDER BY Count) AS Combined_Count
FROM BOOK1
WHERE Type = 'Type' 
  AND Location IN ('CountryA', 'CountryB')
GROUP BY Year, Location, Rand, Rand2, Type
ORDER BY Location ASC, Year ASC;

几个关键点 #

• 如果同组里的两个Rand列值不一样，你得明确怎么处理——比如取最小/最大值，不然GROUP BY会把Rand不同的行当成不同组，合并不了。
• COALESCE是用来处理NULL值的，本来就不适合做字符串聚合，这就是你之前试了没用的原因。

内容的提问来源于stack exchange，提问作者Noah Roos