MySQL 结果为逗号分隔列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/662207/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Results as comma separated list
提问by Glen Solsberry
I need to run a query like:
我需要运行一个查询，如：
SELECT p.id, p.name,
(SELECT name
FROM sites s
WHERE s.id = p.site_id) AS site_list
FROM publications p
But I'd like the sub-select to return a comma separated list, instead of a column of data. Is this even possible, and if so, how?
但我希望子选择返回一个逗号分隔的列表，而不是一列数据。这甚至可能吗，如果可以，怎么办？
回答by Paul Dixon
You can use GROUP_CONCATto perform that, e.g. something like
您可以使用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;
回答by Robert Quinn
Instead of using group concat()you can use just concat()
而不是使用group concat()你可以使用concat()
Select concat(Col1, ',', Col2) as Foo_Bar from Table1;
edit this only works in mySQL; Oracle concat only accepts two arguments. In oracle you can use something like select col1||','||col2||','||col3 as foobar from table1; in sql server you would use + instead of pipes.
编辑这仅适用于 mySQL；Oracle concat 只接受两个参数。在 oracle 中，您可以使用 select col1||','||col2||','||col3 as foobar from table1; 在 sql server 中，您将使用 + 而不是管道。
回答by Ganesa Vijayakumar
Now only I came across this situation and found some more interesting features around GROUP_CONCAT. I hope these details will make you feel interesting.
现在只有我遇到了这种情况，并发现了一些更有趣的功能GROUP_CONCAT。我希望这些细节会让你觉得有趣。
simple GROUP_CONCAT
简单的 GROUP_CONCAT
SELECT GROUP_CONCAT(TaskName)
FROM Tasks;
Result:
结果：
+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName) |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+
GROUP_CONCAT with DISTINCT
GROUP_CONCAT 与 DISTINCT
SELECT GROUP_CONCAT(TaskName)
FROM Tasks;
Result:
结果：
+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName) |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+
GROUP_CONCAT with DISTINCT and ORDER BY
带有 DISTINCT 和 ORDER BY 的 GROUP_CONCAT
SELECT GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC)
FROM Tasks;
Result:
结果：
+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName ORDER BY TaskName DESC) |
+--------------------------------------------------------+
| Take dog for walk,Relax,Paint roof,Feed cats,Do garden |
+--------------------------------------------------------+
GROUP_CONCAT with DISTINCT and SEPARATOR
带有 DISTINCT 和 SEPARATOR 的 GROUP_CONCAT
SELECT GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ')
FROM Tasks;
Result:
结果：
+----------------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName SEPARATOR ' + ') |
+----------------------------------------------------------------+
| Do garden + Feed cats + Paint roof + Relax + Take dog for walk |
+----------------------------------------------------------------+
GROUP_CONCAT and Combining Columns
GROUP_CONCAT 和组合列
SELECT GROUP_CONCAT(TaskId, ') ', TaskName SEPARATOR ' ')
FROM Tasks;
Result:
结果：
+------------------------------------------------------------------------------------+
| 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 and Grouped ResultsAssume that the following are the results before using GROUP_CONCAT
GROUP_CONCAT 和 Grouped Results假设下面是使用前的结果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;
Result:
结果：
+------------------------+----------------------------------------------------------------------------+
| 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 |
+------------------------+----------------------------------------------------------------------------+
回答by Vignesh Chinnaiyan
In my case i have to concatenate all the account number of a person who's mobile number is unique. So i have used the following query to achieve that.
就我而言，我必须连接手机号码唯一的人的所有帐号。所以我使用以下查询来实现这一点。
SELECT GROUP_CONCAT(AccountsNo) as Accounts FROM `tblaccounts` GROUP BY MobileNumber
Query Result is below:
查询结果如下：
Accounts
93348001,97530801,93348001,97530801
89663501
62630701
6227895144840002
60070021
60070020
60070019
60070018
60070017
60070016
60070015
