以逗号分隔的 MySQL 结果列表

我需要运行一个查询，比如:

SELECT p.id, p.name,
(SELECT name
FROM sites s
WHERE s.id = p.site_id) AS site_list
FROM publications p

但是我希望子选择返回一个逗号分隔的列表，而不是一列数据。这可能吗，如果可能，怎么可能？

177099 次浏览
小开
最佳答案

您可以使用 集团 _ 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;
小开

可以只使用 concat()而不使用 group concat()

Select concat(Col1, ',', Col2) as Foo_Bar from Table1;

Edit 这只在 mySQL 中有效; Oracle concat 只接受两个参数。在 Oracle 中，可以使用诸如 select col1 | |’、’| | col2 | |’、’| | col3作为 table1中的 foobar; 在 sql 服务器中，您将使用 + 代替管道。

小开

在我的情况下，我必须连接一个人的所有帐户号码谁的手机号码是唯一的。因此，我使用了以下查询来实现这一点。

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
小开

现在只有我遇到了这种情况，并发现了一些围绕 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 |
+------------------------------------------------------------------+

GROUP _ CONCAT 和 DISTINCT

SELECT GROUP_CONCAT(TaskName)
FROM Tasks;

结果:

+------------------------------------------------------------------+
| GROUP_CONCAT(TaskName)                                           |
+------------------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Take dog for walk,Relax,Feed cats |
+------------------------------------------------------------------+

GROUP _ CONCAT 使用 DISTINCT 和 ORDERBY

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 |
+--------------------------------------------------------+

GROUP _ CONCAT 与 DISTINCT 和 SEPARator

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                       |
+------------------------+----------------------------------------------------------------------------+

提交答案