mysql - SQL GROUP_CONCAT 不适用于 2 表连接

Question

我的查询按预期工作:

查询

SELECT name, date, enddate, abo_name, bill_id
FROM (
    SELECT CONCAT(c.firstname, " ", c.surname) AS name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(b.abo_id) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
    FROM customer c
    LEFT JOIN bill b ON c.id = b.customer_id
    GROUP BY name
) t

输出(JSON)

{
    name: "Max Mustermann",
    date: "2018-09-13,2018-09-27",
    enddate: "2018-09-13,2018-09-28",
    abo_name: "1,2",
    bill_id: "23,27"
},
...

但是现在我需要在abo_name 属性中添加更详细的信息，我需要输出完整的abo 名称。我尝试只向查询添加一个 INNER JOIN，但是 GROUP_CONCAT 不再起作用。

例子如下:

查询

SELECT name, date, enddate, abo_name, bill_id
FROM (
    SELECT CONCAT(c.firstname, " ", c.surname) AS name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(a.name) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
    FROM customer c
    LEFT JOIN bill b ON c.id = b.customer_id
    INNER JOIN abo a ON b.abo_id = a.id
    GROUP BY name
) t

输出(JSON)

{
    name: "Max Mustermann",
    date: "2018-09-13",
    enddate: "2018-09-13",
    abo_name: "Einzelstunde",
    bill_id: "23"
},
{
    name: "Max Mustermann",
    date: "2018-09-27",
    enddate: "2018-09-28",
    abo_name: "Schnupperstunde",
    bill_id: "27"
},

如您所见，数据未合并，并创建了 2 个对象。有人知道为什么会这样吗？

感谢您的宝贵时间和帮助!

Answer 1

abo 有一个名为 name 的列。按表达式聚合或使用不同的别名:

SELECT CONCAT(c.firstname, ' ', c.surname) AS new_name, GROUP_CONCAT(b.date) AS date, GROUP_CONCAT(b.endDate) AS enddate, GROUP_CONCAT(a.name) AS abo_name, GROUP_CONCAT(b.id) AS bill_id
FROM customer c LEFT JOIN
     bill b
     ON c.id = b.customer_id INNER JOIN
     abo a
     ON b.abo_id = a.id
GROUP BY new_name