MySQL 8.0.12如何将多行查询结果转为单行多列展示？

原查询及返回结果 #

原查询语句：

SELECT
    tProgr,
    tLevel,
    pProg_related,
    pLevel 
FROM
    tbl_t t
    LEFT JOIN tbl_p p ON t.tProgr = p.tProgr 
WHERE
    t.tProgr = '2022-0071' 
ORDER BY
    t.tProgr DESC;

返回的多行结果：

-------------------------------------------------------
| tProgr    | tLevel     | pProg_related | pLevel     |
-------------------------------------------------------
| 2022-0071 | Principal  | 2022-0010     | Secondary  |
| 2022-0071 | Principal  | 2022-0065     | Secondary  |
| 2022-0071 | Principal  | 2022-0076     | Secondary  |
| 2022-0071 | Principal  | 2022-0182     | Secondary  |
| 2022-0071 | Principal  | 2022-0223     | Secondary  |
-------------------------------------------------------

需求是将上述多行结果转换为单行多列形式，每组pProg_related和pLevel作为独立列展示。

实现方案 #

这类需求属于行列转置（Pivot），核心思路是先给关联的tbl_p数据行编号，再通过条件聚合提取每一行的对应字段。

通用方案（适配MySQL、PostgreSQL等多数数据库） #

使用窗口函数ROW_NUMBER()给每个tProgr分组下的tbl_p数据编号，再用条件聚合函数MAX(CASE ...)提取对应编号的字段：

SELECT
    t.tProgr,
    t.tLevel,
    MAX(CASE WHEN rn = 1 THEN p.pProg_related END) AS pProg_related_1,
    MAX(CASE WHEN rn = 1 THEN p.pLevel END) AS pLevel_1,
    MAX(CASE WHEN rn = 2 THEN p.pProg_related END) AS pProg_related_2,
    MAX(CASE WHEN rn = 2 THEN p.pLevel END) AS pLevel_2,
    MAX(CASE WHEN rn = 3 THEN p.pProg_related END) AS pProg_related_3,
    MAX(CASE WHEN rn = 3 THEN p.pLevel END) AS pLevel_3,
    MAX(CASE WHEN rn = 4 THEN p.pProg_related END) AS pProg_related_4,
    MAX(CASE WHEN rn = 4 THEN p.pLevel END) AS pLevel_4,
    MAX(CASE WHEN rn = 5 THEN p.pProg_related END) AS pProg_related_5,
    MAX(CASE WHEN rn = 5 THEN p.pLevel END) AS pLevel_5
FROM
    tbl_t t
LEFT JOIN (
    SELECT 
        tProgr, 
        pProg_related, 
        pLevel,
        ROW_NUMBER() OVER (PARTITION BY tProgr ORDER BY pProg_related) AS rn
    FROM tbl_p
) p ON t.tProgr = p.tProgr
WHERE
    t.tProgr = '2022-0071'
GROUP BY
    t.tProgr, t.tLevel
ORDER BY
    t.tProgr DESC;

方案说明 #

1. 子查询中用ROW_NUMBER()给每个tProgr对应的tbl_p数据按pProg_related排序并编号，确保每行数据有唯一标识；
2. 外层通过MAX(CASE ...)根据编号提取对应行的pProg_related和pLevel，并通过GROUP BY聚合为单行；
3. 如果tbl_p中对应tProgr的数据行数超过5行，需要继续添加对应编号的CASE语句；如果行数不固定，可使用对应数据库的动态SQL生成列（比如MySQL用存储过程，SQL Server用动态拼接语句）。

SQL Server 专属方案（使用PIVOT语法） #

如果使用SQL Server，可直接用PIVOT语法简化实现：

WITH numbered_data AS (
    SELECT
        t.tProgr,
        t.tLevel,
        p.pProg_related,
        p.pLevel,
        'pProg_related_' + CAST(ROW_NUMBER() OVER (PARTITION BY t.tProgr ORDER BY p.pProg_related) AS VARCHAR) AS prog_col,
        'pLevel_' + CAST(ROW_NUMBER() OVER (PARTITION BY t.tProgr ORDER BY p.pProg_related) AS VARCHAR) AS level_col
    FROM
        tbl_t t
    LEFT JOIN tbl_p p ON t.tProgr = p.tProgr
    WHERE
        t.tProgr = '2022-0071'
),
prog_pivot AS (
    SELECT *
    FROM numbered_data
    PIVOT (MAX(pProg_related) FOR prog_col IN (pProg_related_1, pProg_related_2, pProg_related_3, pProg_related_4, pProg_related_5)) AS p1
),
level_pivot AS (
    SELECT *
    FROM numbered_data
    PIVOT (MAX(pLevel) FOR level_col IN (pLevel_1, pLevel_2, pLevel_3, pLevel_4, pLevel_5)) AS p2
)
SELECT
    p.tProgr,
    p.tLevel,
    p.pProg_related_1, l.pLevel_1,
    p.pProg_related_2, l.pLevel_2,
    p.pProg_related_3, l.pLevel_3,
    p.pProg_related_4, l.pLevel_4,
    p.pProg_related_5, l.pLevel_5
FROM prog_pivot p
JOIN level_pivot l ON p.tProgr = l.tProgr AND p.tLevel = l.tLevel
ORDER BY p.tProgr DESC;

内容的提问来源于stack exchange，提问作者George A. Custer