SQL查询优化需求：如何将指定formations的prerequis对应的fo.code合并为单行单列（逗号分隔）

嘿，这个需求完全不用写循环！直接用数据库自带的字符串聚合函数就能轻松搞定，不同数据库的实现略有差异，我给你分情况讲清楚：

你当前的查询返回多行是因为每个前置培训对应一条记录，但我们可以用数据库内置的聚合函数，把多个fo.code合并成单个英文逗号分隔的字符串，效率比循环高得多。

1. MySQL / MariaDB #

使用GROUP_CONCAT()函数，修改后的查询语句如下：

SELECT 
  formations.id, 
  formations.titre, 
  GROUP_CONCAT(fo.code SEPARATOR ', ') AS prerequis_codes
FROM formations 
JOIN prerequis ON formations.id = prerequis.id_formation 
JOIN formations fo ON prerequis.id_formation_requi = fo.id 
WHERE formations.id = 24
GROUP BY formations.id, formations.titre;

• GROUP_CONCAT()会自动把同一主培训分组内的fo.code用指定分隔符拼接
• 必须搭配GROUP BY子句，按主培训的id和titre分组，确保结果只返回一行主培训数据

2. PostgreSQL #

使用STRING_AGG()函数，语法更简洁：

SELECT 
  formations.id, 
  formations.titre, 
  STRING_AGG(fo.code, ', ') AS prerequis_codes
FROM formations 
JOIN prerequis ON formations.id = prerequis.id_formation 
JOIN formations fo ON prerequis.id_formation_requi = fo.id 
WHERE formations.id = 24
GROUP BY formations.id, formations.titre;

3. SQL Server #

• 2017及以上版本：支持STRING_AGG()，写法和PostgreSQL一致：

SELECT 
  formations.id, 
  formations.titre, 
  STRING_AGG(fo.code, ', ') AS prerequis_codes
FROM formations 
JOIN prerequis ON formations.id = prerequis.id_formation 
JOIN formations fo ON prerequis.id_formation_requi = fo.id 
WHERE formations.id = 24
GROUP BY formations.id, formations.titre;

• 2016及以下旧版本：可以用FOR XML PATH的方式实现：

SELECT 
  f.id, 
  f.titre,
  STUFF(
    (SELECT ', ' + fo.code 
     FROM prerequis p
     JOIN formations fo ON p.id_formation_requi = fo.id
     WHERE p.id_formation = f.id
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
  ) AS prerequis_codes
FROM formations f
WHERE f.id = 24;

• STUFF()的作用是去掉拼接后字符串开头多余的,

4. Oracle #

使用LISTAGG()函数，还能指定拼接顺序：

SELECT 
  formations.id, 
  formations.titre, 
  LISTAGG(fo.code, ', ') WITHIN GROUP (ORDER BY fo.code) AS prerequis_codes
FROM formations 
JOIN prerequis ON formations.id = prerequis.id_formation 
JOIN formations fo ON prerequis.id_formation_requi = fo.id 
WHERE formations.id = 24
GROUP BY formations.id, formations.titre;

• WITHIN GROUP (ORDER BY ...)可以指定code的拼接顺序，可选但能保证结果稳定

按照对应数据库的语法修改后，查询会返回一行数据，其中prerequis_codes列就是所有前置培训code用英文逗号分隔的字符串啦~

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