没问题！这个需求在SQL开发里太常见了，本质就是用分组聚合的字符串拼接函数来实现。不同数据库的函数名不一样，我给你分主流数据库逐一说明：

MySQL/MariaDB #

用GROUP_CONCAT()函数就能轻松搞定，示例代码如下：

SELECT 
  mountain_name,
  GROUP_CONCAT(country SEPARATOR ', ') AS countries
FROM mountains
GROUP BY mountain_name;

这里GROUP_CONCAT会把同一山脉组下的所有country值拼接起来，SEPARATOR ', '指定用逗号加空格分隔，你也可以根据需求改成纯逗号','。

PostgreSQL #

PostgreSQL用STRING_AGG()函数，用法很直观：

SELECT 
  mountain_name,
  STRING_AGG(country, ', ') AS countries
FROM mountains
GROUP BY mountain_name;

如果需要让拼接后的国家名称按字母排序，还可以加个排序参数：

SELECT 
  mountain_name,
  STRING_AGG(country, ', ' ORDER BY country) AS countries
FROM mountains
GROUP BY mountain_name;

SQL Server #

SQL Server 2017及以上版本支持STRING_AGG()，写法和PostgreSQL类似：

SELECT 
  mountain_name,
  STRING_AGG(country, ', ') AS countries
FROM mountains
GROUP BY mountain_name;

要是你用的是2017之前的老版本，就得用FOR XML PATH的传统方法：

SELECT 
  DISTINCT mountain_name,
  STUFF(
    (SELECT ', ' + country 
     FROM mountains m2 
     WHERE m2.mountain_name = m1.mountain_name 
     FOR XML PATH('')),
    1, 2, ''
  ) AS countries
FROM mountains m1;

简单解释下：子查询用FOR XML PATH('')把同组国家拼接成带, 前缀的字符串，再用STUFF去掉开头多余的, 。

Oracle #

Oracle用LISTAGG()函数实现：

SELECT 
  mountain_name,
  LISTAGG(country, ', ') WITHIN GROUP (ORDER BY country) AS countries
FROM mountains
GROUP BY mountain_name;

WITHIN GROUP (ORDER BY country)用来让拼接后的国家按名称排序，不需要排序的话可以直接去掉这个部分。

内容的提问来源于stack exchange，提问作者Giorgos Dagalakis