MySQL：使用LEFT JOIN的GROUP_CONCAT函数

Question

我在使用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.');

注意这个帮助台数据库有两个工单，每个工单都有两个解决方案条目。我的目标是使用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;

上述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.

请注意，它返回票1的信息，并显示票1和票2的解决方案条目。

我做错了什么？谢谢！

- Nick

Answer 1

使用：

   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

备用：

   SELECT t.*,
          (SELECT GROUP_CONCAT(s.soution)
             FROM SOLUTIONS s 
            WHERE s.ticket_id = t.ticket_id) AS combinedsolutions
     FROM TICKETS t

- OMG Ponies

---

为什么尼克的方法不起作用？显然它没有起作用，但似乎它应该能够。你能向我解释一下吗？ - pbarney

到目前为止，我理解 group_contact 不关心查询的部分，它会对所有中间结果进行分组。 - jnovacho

我原先认为我的 join 操作有问题，但实际上我只是漏了一个 GROUP BY 语句，因此 GROUP_CONCAT() 函数没有聚合数值。希望这能节省其他人的时间，就像我花费一小时才明白一样。 - Dylan Valade

这里提供的“替代”答案对于更大的数据集来说性能要好得多。 - Andy Lorenz

备选答案看起来更简洁。那就是我要的答案。而且它有效！ - Alexander Flenniken

Answer 2

你只需要添加一个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;

- Frederic WOEHL

---

这对我来说也是一样的情况。被接受的答案也起作用，但使我的查询变得更难读。添加 GROUP_BY ... 给了我想要的行为。 - Mark

Answer 3

我认为@Dylan Valade的评论是最简单的答案，所以我将其作为另一个答案发布：只需在OP的SELECT中添加GROUP BY Tickets.id即可解决问题。它解决了我的问题。

然而，对于不小的数据库，特别是如果Tickets.id上有任何谓词，被接受的答案似乎不涉及总表扫描，因此虽然前一段返回正确的结果，但在我的情况下它似乎效率要低得多。

- ghr