如何使用T-SQL按列值升序排序生成新表或视图？

嘿，我来给你搞定这个需求——把每行的Val1到Val5按升序重新排列。下面分两种常见数据库给出实现方案：

1. SQL Server 实现方案 #

首先先模拟你的原表数据：

-- 创建原表并插入示例数据
CREATE TABLE YourTable (
    Date DATE,
    [Desc] VARCHAR(10),
    Val1 INT,
    Val2 INT,
    Val3 INT,
    Val4 INT,
    Val5 INT
);

INSERT INTO YourTable VALUES
('2018-04-18', 'A', 1, 3, 18, 2, 24),
('2018-04-18', 'B', 90, 18, 2, 33, 40),
('2018-04-18', 'C', 12, 45, 11, 22, 3),
('2018-04-18', 'D', 10, 33, 8, 15, 12),
('2018-04-18', 'E', 81, 77, 24, 1, 15);

然后用UNPIVOT+PIVOT组合实现排序重排：

SELECT 
    Date,
    [Desc],
    [1] AS Val1,
    [2] AS Val2,
    [3] AS Val3,
    [4] AS Val4,
    [5] AS Val5
FROM (
    SELECT 
        Date,
        [Desc],
        Val,
        -- 给每个分组内的Val按升序分配序号
        ROW_NUMBER() OVER (PARTITION BY Date, [Desc] ORDER BY Val ASC) AS RowNum
    FROM YourTable
    -- 把Val1-Val5列转成多行数据
    UNPIVOT (
        Val FOR ValCols IN (Val1, Val2, Val3, Val4, Val5)
    ) AS Unpvt
) AS PivotSource
-- 把带序号的Val转回列
PIVOT (
    MAX(Val) FOR RowNum IN ([1], [2], [3], [4], [5])
) AS Pvt
ORDER BY Date, [Desc];

逻辑说明 #

• UNPIVOT 阶段：把每行的5个Val列拆成5行，每行对应一个Val值和原有的Date、Desc，方便后续分组排序。
• ROW_NUMBER() 阶段：在每个Date+Desc的分组里，给Val值按升序分配1-5的序号，最小的Val得到序号1，以此类推。
• PIVOT 阶段：把带有序号的Val值重新转回列，序号1对应新的Val1，最终得到每行Val列升序排列的结果。

查询后的结果如下：

2. MySQL 实现方案 #

MySQL没有内置的UNPIVOT和PIVOT函数，我们可以用UNION ALL模拟拆分行，用条件聚合模拟转列：

SELECT 
    Date,
    `Desc`,
    MAX(CASE WHEN RowNum = 1 THEN Val END) AS Val1,
    MAX(CASE WHEN RowNum = 2 THEN Val END) AS Val2,
    MAX(CASE WHEN RowNum = 3 THEN Val END) AS Val3,
    MAX(CASE WHEN RowNum = 4 THEN Val END) AS Val4,
    MAX(CASE WHEN RowNum = 5 THEN Val END) AS Val5
FROM (
    SELECT 
        Date,
        `Desc`,
        Val,
        ROW_NUMBER() OVER (PARTITION BY Date, `Desc` ORDER BY Val ASC) AS RowNum
    FROM (
        -- 用UNION ALL把Val1-Val5拆成多行
        SELECT Date, `Desc`, Val1 AS Val FROM YourTable
        UNION ALL
        SELECT Date, `Desc`, Val2 AS Val FROM YourTable
        UNION ALL
        SELECT Date, `Desc`, Val3 AS Val FROM YourTable
        UNION ALL
        SELECT Date, `Desc`, Val4 AS Val FROM YourTable
        UNION ALL
        SELECT Date, `Desc`, Val5 AS Val FROM YourTable
    ) AS Unpvt
) AS PivotSource
GROUP BY Date, `Desc`
ORDER BY Date, `Desc`;

这个逻辑和SQL Server版本一致，只是用了MySQL支持的语法来实现相同的效果，最终输出结果也和上面的表格一致。

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