如何编写SQL将单列分组数据转换为多列并排展示

API 探路者

2026-10-11

实现思路

核心是把两个Class分组下的记录按内部顺序横向拼接，操作逻辑如下：

用窗口函数ROW_NUMBER()给每个Class内部的行按ID排序生成唯一匹配序号
分别筛选Class=1和Class=2的两个子集，按生成的序号做等值连接，就能得到并排展示的效果

可直接使用的SQL语句

以下SQL适配MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的主流数据库：

WITH ranked_data AS (
    SELECT 
        Class,
        Number,
        ROW_NUMBER() OVER (PARTITION BY Class ORDER BY ID) AS rn
    FROM 替换为你的实际表名
)
SELECT 
    t1.Class AS Column1,
    t1.Number AS Number,
    t2.Class AS Column2,
    t2.Number AS Number
FROM ranked_data t1
INNER JOIN ranked_data t2 
    ON t1.rn = t2.rn 
    AND t1.Class = 1 
    AND t2.Class = 2;

如果使用的是不支持CTE的低版本MySQL，可以用子查询替代实现：

SELECT 
    t1.Class AS Column1,
    t1.Number AS Number,
    t2.Class AS Column2,
    t2.Number AS Number
FROM 
    (SELECT Class, Number, @rn1:=@rn1+1 AS rn FROM 替换为你的实际表名 WHERE Class=1 ORDER BY ID, (SELECT @rn1:=0)) t1
INNER JOIN 
    (SELECT Class, Number, @rn2:=@rn2+1 AS rn FROM 替换为你的实际表名 WHERE Class=2 ORDER BY ID, (SELECT @rn2:=0)) t2
ON t1.rn = t2.rn;