MySQL：GROUP_CONCAT 与 LEFT JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4455958/
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: GROUP_CONCAT with LEFT JOIN
提问by Nick
I'm experiencing a problem with MySQL's "GROUP_CONCAT" function. I will illustrate my problem using a simple help desk database:
我在使用 MySQL 的“GROUP_CONCAT”函数时遇到问题。我将使用一个简单的帮助台数据库来说明我的问题：
CREATE TABLE Tickets (
id INTEGER NOT NULL PRIMARY KEY,
requester_name VARCHAR(255) NOT NULL,
description TEXT NOT NULL);
CREATE TABLE Solutions (
id INTEGER NOT NULL PRIMARY KEY,
ticket_id INTEGER NOT NULL,
technician_name VARCHAR(255) NOT NULL,
solution TEXT NOT NULL,
FOREIGN KEY (ticket_id) REFERENCES Tickets.id);
INSERT INTO Tickets VALUES(1, 'John Doe', 'My computer is not booting.');
INSERT INTO Tickets VALUES(2, 'Jane Doe', 'My browser keeps crashing.');
INSERT INTO Solutions VALUES(1, 1, 'Technician A', 'I tried to solve this but was unable to. I will pass this on to Technician B since he is more experienced than I am.');
INSERT INTO Solutions VALUES(2, 1, 'Technician B', 'I reseated the RAM and that fixed the problem.');
INSERT INTO Solutions VALUES(3, 2, 'Technician A', 'I was unable to figure this out. I will again pass this on to Technician B.');
INSERT INTO Solutions VALUES(4, 2, 'Technician B', 'I re-installed the browser and that fixed the problem.');
Notice that this help desk database has two tickets, each with two solution entries. My goal is to use a SELECT statement to create a list of all of the tickets in the database with their corrosponding solution entries. This is the SELECT statement I'm using:
请注意，此帮助台数据库有两张票，每张票都有两个解决方案条目。我的目标是使用 SELECT 语句创建数据库中所有票证及其对应解决方案条目的列表。这是我正在使用的 SELECT 语句：
SELECT Tickets.*, GROUP_CONCAT(Solutions.solution) AS CombinedSolutions
FROM Tickets
LEFT JOIN Solutions ON Tickets.id = Solutions.ticket_id
ORDER BY Tickets.id;
The problem with the above SELECT statement is it's returning only one row:
上面 SELECT 语句的问题是它只返回一行：
id: 1
requester_name: John Doe
description: My computer is not booting.
CombinedSolutions: I tried to solve this but was unable to. I will pass this on to Technician B since he is more experienced than I am.,I reseated the RAM and that fixed the problem.,I was unable to figure this out. I will again pass this on to Technician B.,I re-installed the browser and that fixed the problem.
Notice that it's returning ticket 1's information with both ticket 1's and ticket 2's solution entries.
请注意，它返回了票证 1 的信息以及票证 1 和票证 2 的解决方案条目。
What am I doing wrong? Thanks!
我究竟做错了什么？谢谢！
回答by OMG Ponies
Use:
用：
SELECT t.*,
x.combinedsolutions
FROM TICKETS t
LEFT JOIN (SELECT s.ticket_id,
GROUP_CONCAT(s.soution) AS combinedsolutions
FROM SOLUTIONS s
GROUP BY s.ticket_id) x ON x.ticket_id = t.ticket_id
Alternate:
备用：
SELECT t.*,
(SELECT GROUP_CONCAT(s.soution)
FROM SOLUTIONS s
WHERE s.ticket_id = t.ticket_id) AS combinedsolutions
FROM TICKETS t
回答by ghr
I think @Dylan Valade's comment is the simplest answer so I'm posting it as another answer: simply adding a GROUP BY Tickets.id to the OP's SELECT should fix the issue. It fixed my own issue.
我认为@Dylan Valade 的评论是最简单的答案，所以我将其作为另一个答案发布：只需将 GROUP BY Tickets.id 添加到 OP 的 SELECT 即可解决问题。它解决了我自己的问题。
However, for databases that are not small the accepted answer, especially if there are any predicates on Tickets.id appears to not involve a total table scan and so while the previous paragraph returns the correct results it appears to be much less efficient in my case.
但是，对于不小的数据库，可接受的答案，特别是如果 Tickets.id 上的任何谓词似乎不涉及全表扫描，因此虽然上一段返回正确的结果，但在我的情况下似乎效率低得多.
回答by Frederic WOEHL
You just need to add a GROUP_BY :
你只需要添加一个 GROUP_BY ：
SELECT Tickets.*, GROUP_CONCAT(Solutions.solution) AS CombinedSolutions FROM Tickets
LEFT JOIN Solutions ON Tickets.id = Solutions.ticket_id
GROUP_BY Tickets.id
ORDER BY Tickets.id;
