MySQLSELECT FieldA , GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
Oracle&DB2SELECT FieldA , LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
PostgreSQLSELECT FieldA , STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
SQLServer
SQLServer≥2017&Azure SQLSELECT FieldA , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;
SQLServer≤2016(包括cte以鼓励干原理)WITH CTE_TableName AS (
SELECT FieldA, FieldB FROM TableName)SELECT t0.FieldA , STUFF((
SELECT ',' + t1.FieldB FROM CTE_TableName t1 WHERE t1.FieldA = t0.FieldA
ORDER BY t1.FieldB FOR XML PATH('')), 1, LEN(','), '')
AS FieldBs FROM CTE_TableName t0 GROUP BY t0.FieldA ORDER BY FieldA;
SQLite
排序需要CTE或子查询WITH CTE_TableName AS (
SELECT FieldA, FieldB FROM TableName ORDER BY FieldA, FieldB)SELECT FieldA
, GROUP_CONCAT(FieldB, ',') AS FieldBs FROM CTE_TableName GROUP BY FieldA ORDER BY FieldA;
不订货SELECT FieldA , GROUP_CONCAT(FieldB, ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;