mysql - 如何将同一ID的不同负责人姓名分组

让我解释一下我需要什么：对于唯一的num_contract，我有不同的name_user，但是，它按行显示一个负责人。

SELECT a.num_contract, g.name_user
FROM `contract` AS a
INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
INNER JOIN `user` h ON h.id_user = g.id_user
ORDER BY `a`.`id_contract` DESC

输出值

a.num_contract | g.name_user
0857             John
0857             Mark
0856             Ann
0855             Louis
0855             Peter
0855             Alisson

如上所示，a.num_contract是唯一的，并在每行中显示name_user。

我的期望：

a.num_contract | g.name_user
0857             John, Mark
0856             Ann
0855             Louis, Peter, Alisson

要么

a.num_contract | g.name_user | g.name_user | g.name_user
0857             John          Mark          NULL
0856             Ann           NULL          NULL
0855             Louis         Peter         Alisson

我读了一些有关数据透视表的内容，但不确定如何实现。
谢谢，

最佳答案

如果希望将用户列表作为一列，则可以将GROUP_BY与GROUP_CONCAT一起使用。

SELECT
    a.num_contract AS Contract_No,
    GROUP_CONCAT(g.name_user) AS Users
FROM `contract` AS a
INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
INNER JOIN `user` h ON h.id_user = g.id_user
GROUP BY a.num_contract
ORDER BY `a`.`id_contract` DESC;

Output:
Contract_No    | Users
--------------------------------------
0857           | John,Mark
0856           | Ann
0855           | Louis,Peter,Alisson

如果要将用户列为单独的列，则可以在上述查询中使用rank变量或SUBSTRING_INDEX以获得所需的结果。

SELECT
    Contract_No,
    SUBSTRING_INDEX(Users,'$$',1) AS User_1,
    CASE WHEN Total_Users >=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Users,'$$',2),' ',-1) END AS User_2,
    CASE WHEN Total_Users>=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(Users, '$$',3),' ',-1) END AS User_3
FROM (
    SELECT
        a.num_contract AS Contract_No,
        GROUP_CONCAT(g.name_user SEPARATOR '$$') AS Users,
        COUNT(0) AS Total_Users
    FROM `contract` AS a
    INNER JOIN `resp_contract` g ON g.id_contract = a.id_contract
    INNER JOIN `user` h ON h.id_user = g.id_user
    GROUP BY a.num_contract
    ORDER BY `a`.`id_contract` DESC
) AS t;

Output:
Contract_No    | User_1      | User_2     | User_3
-----------------------------------------------------
0857           | John        | Mark       |
0856           | Ann         |            |
0855           | Louis       | Peter      |  Alisson