MySQL 从一行中的其他表中选择多行和多列

Question

我正在寻找一种构建给定表格的方法:

TABLE: group                    TABLE: person
-------------------------       -------------------------------------------------
| Group_id | Group_name |      | Person_id | Person_name | Person_fid | Group_id |
-------------------------       -------------------------------------------------
|    1     | Group44    |      |     1     |     John    |      2     |    1     |
-------------------------       -------------------------------------------------
|    2     | Best Group |      |     2     |    George   |      1     |    1     |
-------------------------       -------------------------------------------------
                               |     3     |    Peter    |      2     |    2     |

(表 person 有其他列，如 person_nickname、person_status 等)

结果如下:(Groupname、person_fid1 列、person_fid2 列)

 -------------------------------------------------------------------
|  Groupname   | fid1_pname | fid 1_pstat | fid2_pname | fid2_pstat |
 -------------------------------------------------------------------
|   Group44    |   George   |      1      |    John    |      0     |
 -------------------------------------------------------------------
|  Best Group  |   NULL     |   NULL      |    Peter   |      1     |
 -------------------------------------------------------------------

我需要生成 6 个不同的 Person_fids 列。

如果组中没有具有特定 fid 的人员，则这些列可能会显示 NULL。有办法做到这一点吗？

我尝试过使用:

SELECT group.name, MAX(CASE WHEN person.Person_fid = 1 THEN |get column infos|
ELSE NULL, NULL, NULL END) 3 columns fid1, 
MAX(CASE WHEN person.Person_fid = 2 THEN |get column infos|
ELSE NULL, NULL, NULL END) 3 columns fid2

但我无法走得太远，因为我什至不知道如何搜索此类内容。

Answer 1

首先将您的表Group更改为其他内容，例如Group_info、person_group等。 as group 是一个保留字(它本身有一些功能)。

您需要的sql可能如下(未经测试，但应该有效)

SELECT 
    group_name, 
    t1.person_name as name_1, t1.person_status as stat_1,
    t2.person_name as name_2, t2.person_status as stat_2,
    t3.person_name as name_3, t3.person_status as stat_3,
    t4.person_name as name_4, t4.person_status as stat_4,
    t5.person_name as name_5, t5.person_status as stat_5,
    t6.person_name as name_6, t6.person_status as stat_6,
FROM group_info gi
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=1) as t1 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=2) as t2 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=3) as t3 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=4) as t4 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=5) as t5 USING (group_id)
LEFT JOIN (SELECT person_name,person_status FROM person WHERE person_fid=6) as t6 USING (group_id)