MySQL：如何将单表按类型转换为两列并消除NULL值？

解决方案 #

问题原因 #

原来的CASE语句是逐行处理单条记录，每条记录只能匹配一种type，因此另一列必然为NULL。要实现两列对齐无NULL的效果，需要将两种类型的数据分别按顺序编号，再通过编号关联两组数据。

适用于MySQL 8.0+的查询（支持窗口函数） #

WITH muscle_cars AS (
    SELECT cars, ROW_NUMBER() OVER (ORDER BY cars) AS rn
    FROM carlist
    WHERE type = 'M'
), super_cars AS (
    SELECT cars, ROW_NUMBER() OVER (ORDER BY cars) AS rn
    FROM carlist
    WHERE type = 'S'
)
SELECT 
    mc.cars AS Musclecar,
    sc.cars AS Supercar
FROM muscle_cars mc
JOIN super_cars sc ON mc.rn = sc.rn;

适用于MySQL 5.x的查询（无CTE支持时） #

SELECT 
    mc.cars AS Musclecar,
    sc.cars AS Supercar
FROM (
    SELECT cars, @row_m := @row_m + 1 AS rn
    FROM carlist, (SELECT @row_m := 0) init
    WHERE type = 'M'
    ORDER BY cars
) mc
JOIN (
    SELECT cars, @row_s := @row_s + 1 AS rn
    FROM carlist, (SELECT @row_s := 0) init
    WHERE type = 'S'
    ORDER BY cars
) sc ON mc.rn = sc.rn;

说明 #

• 两个查询都先为type='M'（肌肉车）和type='S'（超跑）的车辆生成递增行号rn，行号顺序由ORDER BY cars控制，你可以根据需求修改排序字段。
• 通过行号rn关联两组数据，就能得到无NULL的对齐结果。

最终结果 #

执行上述查询后，将得到与预期一致的输出：

内容的提问来源于stack exchange，提问作者vai.becker