我需要运行这样的查询：
SELECT p.id, p.name,
(SELECT name
FROM sites s
WHERE s.id = p.site_id) AS site_list
FROM publications p
但我希望子查询返回一个逗号分隔的列表，而不是一列数据。这是否可能，如果是，如何实现？
MySQL结果作为逗号分隔列表
150
- gms8994
4个回答
280
您可以使用 GROUP_CONCAT 来实现这个功能，例如：
SELECT p.id, p.name, GROUP_CONCAT(s.name) AS site_list
FROM sites s
INNER JOIN publications p ON(s.id = p.site_id)
GROUP BY p.id, p.name;
- Paul Dixon
3
29
现在我遇到了这种情况，并发现一些有趣的与GROUP_CONCAT相关的特性。希望这些细节会让您感到有趣。
简单的GROUP_CONCAT
SELECT GROUP_CONCAT(TaskName)
FROM Tasks;
结果：
+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName) |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+
使用DISTINCT的GROUP_CONCAT
SELECT GROUP_CONCAT(TaskName)
FROM Tasks;
结果：
+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName) |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+
使用DISTINCT和ORDER BY的GROUP_CONCAT
SELECT GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC)
FROM Tasks;
结果：
+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) |
+--------------------------------------------------------+
| Take dog for walk,Relax,Paint roof,Feed cats,Do garden |
+--------------------------------------------------------+
使用DISTINCT和SEPARATOR的GROUP_CONCAT
SELECT GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ')
FROM Tasks;
结果：
+----------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') |
+----------------------------------------------------------------+
| Do garden + Feed cats + Paint roof + Relax + Take dog for walk |
+----------------------------------------------------------------+
GROUP_CONCAT和组合列
SELECT GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ')
FROM Tasks;
结果：
+------------------------------------------------------------------------------------+
| GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ') |
+------------------------------------------------------------------------------------+
| 1) Do garden 2) Feed cats 3) Paint roof 4) Take dog for walk 5) Relax 6) Feed cats |
+------------------------------------------------------------------------------------+
GROUP_CONCAT和分组结果
假设在使用GROUP_CONCAT之前以下是结果。
+------------------------+--------------------------+
| ArtistName | AlbumName |
+------------------------+--------------------------+
| Iron Maiden | Powerslave |
| AC/DC | Powerage |
| Jim Reeves | Singing Down the Lane |
| Devin Townsend | Ziltoid the Omniscient |
| Devin Townsend | Casualties of Cool |
| Devin Townsend | Epicloud |
| Iron Maiden | Somewhere in Time |
| Iron Maiden | Piece of Mind |
| Iron Maiden | Killers |
| Iron Maiden | No Prayer for the Dying |
| The Script | No Sound Without Silence |
| Buddy Rich | Big Swing Face |
| Michael Learns to Rock | Blue Night |
| Michael Learns to Rock | Eternity |
| Michael Learns to Rock | Scandinavia |
| Tom Jones | Long Lost Suitcase |
| Tom Jones | Praise and Blame |
| Tom Jones | Along Came Jones |
| Allan Holdsworth | All Night Wrong |
| Allan Holdsworth | The Sixteen Men of Tain |
+------------------------+--------------------------+
USE Music;
SELECT ar.ArtistName,
GROUP_CONCAT(al.AlbumName)
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;
结果：
+------------------------+----------------------------------------------------------------------------+
| ArtistName | GROUP_CONCAT(al.AlbumName) |
+------------------------+----------------------------------------------------------------------------+
| AC/DC | Powerage |
| Allan Holdsworth | All Night Wrong,The Sixteen Men of Tain |
| Buddy Rich | Big Swing Face |
| Devin Townsend | Epicloud,Ziltoid the Omniscient,Casualties of Cool |
| Iron Maiden | Somewhere in Time,Piece of Mind,Powerslave,Killers,No Prayer for the Dying |
| Jim Reeves | Singing Down the Lane |
| Michael Learns to Rock | Eternity,Scandinavia,Blue Night |
| The Script | No Sound Without Silence |
| Tom Jones | Long Lost Suitcase,Praise and Blame,Along Came Jones |
+------------------------+----------------------------------------------------------------------------+
- Ganesa Vijayakumar
1
它有限制，可能不超过65个结果。 - ani0904071
12
不要使用group concat()，而是使用concat()
Select concat(Col1, ',', Col2) as Foo_Bar from Table1;
只有在 MySQL 中，编辑（字符串拼接）操作才能正常工作。而 Oracle 的 concat 函数仅接受两个参数。在 Oracle 中，您可以使用类似于 select col1||','||col2||','||col3 as foobar from table1; 的语句实现。而在 SQL Server 中，您需要使用 + 号代替竖杠来进行字符串拼接。
- Robert Quinn
1
3在使用GROUP BY时，这种情况通常不起作用，而GROUP_CONCAT()函数会将单个列的内容连接起来。 - Aram Paronikyan
3
在我的情况下，我需要将所有拥有唯一手机号码的人的帐户号码连接起来。因此，我使用了以下查询来实现这一目的。
SELECT GROUP_CONCAT(AccountsNo) as Accounts FROM `tblaccounts` GROUP BY MobileNumber
查询结果如下：
Accounts
93348001,97530801,93348001,97530801
89663501
62630701
6227895144840002
60070021
60070020
60070019
60070018
60070017
60070016
60070015
- Vignesh Chinnaiyan
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
GROUP_CONCAT（CAST（s.name AS CHAR）），否则它将只返回一些完全无用的内容，如[BLOB - 20 Bytes]。 - devios1