MySQL新手求助：如何将3个不同行数的查询结果按列展示

场景说明 #

你有一张Scores表，结构及数据如下：

Scores
NAME    | SCORE
jack    |  1 
mike    |  3
kate    |  2
able    |  1
jill    |  2
seth    |  1

需要将三个针对不同分数的查询结果（行数不同）按列对齐展示，最终输出如下格式（空缺为NULL或空字符串均可）：

Ones     | Twos      | Threes
______________________________
jack     | kate      | mike 
able     | jill      |
seth     |           |

解决方案 #

核心思路是给每个分数分组内的记录生成连续行号，再通过行号关联三个分组的结果，实现列对齐。

方案1：MySQL 8.0及以上版本（支持窗口函数）

使用ROW_NUMBER()窗口函数为每个分组的记录生成行号，再通过行号左连接三个结果集：

SELECT 
    ones.name AS Ones,
    IFNULL(twos.name, '') AS Twos, -- 把NULL转为空字符串，可选
    IFNULL(threes.name, '') AS Threes -- 把NULL转为空字符串，可选
FROM 
    (SELECT name, ROW_NUMBER() OVER () AS rn FROM scores WHERE score = 1) ones
LEFT JOIN 
    (SELECT name, ROW_NUMBER() OVER () AS rn FROM scores WHERE score = 2) twos 
    ON ones.rn = twos.rn
LEFT JOIN 
    (SELECT name, ROW_NUMBER() OVER () AS rn FROM scores WHERE score = 3) threes 
    ON ones.rn = threes.rn;

方案2：MySQL 5.x版本（不支持窗口函数）

使用用户变量手动生成行号，逻辑和方案1一致：

SELECT 
    ones.name AS Ones,
    IFNULL(twos.name, '') AS Twos,
    IFNULL(threes.name, '') AS Threes
FROM 
    (SELECT name, @rn1 := @rn1 + 1 AS rn FROM scores, (SELECT @rn1 := 0) init WHERE score = 1) ones
LEFT JOIN 
    (SELECT name, @rn2 := @rn2 + 1 AS rn FROM scores, (SELECT @rn2 := 0) init WHERE score = 2) twos 
    ON ones.rn = twos.rn
LEFT JOIN 
    (SELECT name, @rn3 := @rn3 + 1 AS rn FROM scores, (SELECT @rn3 := 0) init WHERE score = 3) threes 
    ON ones.rn = threes.rn;

说明 #

• 以分数为1的结果集作为主表（因为它的行数最多），左连接另外两个结果集，确保所有行都能展示，行数少的分组在无对应行时显示空值或NULL。
• IFNULL()函数用于将NULL替换为空字符串，如果你保留NULL可以去掉这个函数。

内容的提问来源于stack exchange，提问作者Peter Smith