php - 按 2 个 id 对输出进行分组

Question

我有一个问题。我有 3 个表格，如下所示:

表公司

 id_comp     comp_name       
     1        company1       
     2        company2                
     3        company3                
     4        company4              

主表

 id_main     code       description     id_comp
     1        100        Project 1         1
     2        200        Project 2         1
     3        300        Project 3         2
     4        400        Project 4         3

表子主

id_sub_main     sub_code    sub_description   id_main
       1        101            Project 1.1      1
       2        102            Project 1.2      1
       3        201            Project 2.1      2
       4        202            Project 2.2      2
       5        203            Project 2.3      2
       6        401            Project 4.1      4
       7        402            Project 4.2      4

问题:生成如下所示表格的最佳 mysql 或 php 代码是什么:

company    code    description    sub_code       sub_description
company1   100       Project 1      101            Project 1.1
                                    102            Project 1.2
           200       Project 2      201            Project 2.1
                                    202            Project 2.2
                                    203            Project 2.3
company3   400       Project 4      401            Project 4.1
                                    402            Project 4.2

我与 Neels 代码混在一起的答案:

company    code    description    sub_code       sub_description
1           100       Project 1      101            Project 1.1
1                                    102            Project 1.2
1           200       Project 2      201            Project 2.1
1                                    202            Project 2.2
1                                    203            Project 2.3
3           400       Project 4      401            Project 4.1
                                     402            Project 4.2

Answer 1

您可以试试这个 MySQL 查询:

SELECT company.comp_name,
GROUP_CONCAT(jointable.code ORDER BY jointable.code SEPARATOR '\n') as code,
GROUP_CONCAT(jointable.description ORDER BY jointable.code SEPARATOR '\n') as description,
GROUP_CONCAT(jointable.sub_code ORDER BY jointable.code SEPARATOR '\n') as sub_code,
GROUP_CONCAT(jointable.sub_description ORDER BY jointable.code SEPARATOR '\n') as sub_description
FROM
(SELECT main.id_comp,
main.code,
main.description,
GROUP_CONCAT(sub_main.sub_code ORDER BY sub_main.sub_code SEPARATOR '\n') as sub_code,
GROUP_CONCAT(sub_main.sub_description ORDER BY sub_main.sub_code SEPARATOR '\n') as sub_description
FROM main
INNER JOIN sub_main ON main.id_main = sub_main.id_main
GROUP BY main.id_main) jointable
INNER JOIN company ON company.id_comp = jointable.id_comp
GROUP BY company.id_comp;

在此处查看有效的 SQL Fiddle:

http://www.sqlfiddle.com/#!2/84e7b/10