MySQL: GROUP_CONCAT与左连接

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的信息和票据2的解决方案条目。

What am I doing wrong? Thanks!

我做错了什么?谢谢!

2 个解决方案

#1

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

#2

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的评论是最简单的答案，所以我将它作为另一个答案发布:简单地按票添加一个组。OP的SELECT的id应该会修复这个问题。它解决了我自己的问题。

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.

但是，对于不小的数据库，这是可接受的答案，特别是如果票据上有任何谓词的话。id似乎不涉及整个表扫描，因此尽管前面的段落返回了正确的结果，但在我的例子中，它的效率似乎要低得多。