请求编写SQL实现同订单下FBH列降序转置成行的存储过程

需求概述 #

当Order no.（订单编号）相同时，需将FBH列的数据从列转置为行，且按从大到小的顺序排列。

原始数据表 #

Order no.   Column 1    Column 2    FBH
18046352-3  A       0.40
18046352-3  A       0.41
18046352-3  A       0.45
18046352-3      B   0.43
18046352-3      B   0.42
18046352-3      B   0.43
18066235-4  C   D   0.27
12345678-1  E   F   0.71

期望结果表 #

Order no.   Column 1    Column 2    FBH-1   FBH-2   FBH-3   FBH-4   FBH-5   FBH-6
18046352-3  A   B   0.45    0.43    0.43    0.42    0.41    0.40
18066235-4  C   D   0.27                    
12345678-1  E   F   0.71                    

注：示例期望结果中的FBH-2为0.44是笔误，实际按原始数据降序应为0.43

SQL解决方案 #

方案1：静态SQL（适用于已知最大FBH数量的场景，如本例最多6个） #

适用于SQL Server、PostgreSQL等支持窗口函数和PIVOT的数据库：

SELECT 
    [Order no.],
    MAX([Column 1]) AS [Column 1],
    MAX([Column 2]) AS [Column 2],
    [1] AS [FBH-1],
    [2] AS [FBH-2],
    [3] AS [FBH-3],
    [4] AS [FBH-4],
    [5] AS [FBH-5],
    [6] AS [FBH-6]
FROM (
    SELECT 
        [Order no.],
        [Column 1],
        [Column 2],
        FBH,
        -- 按订单分组，给FBH降序排序列号
        ROW_NUMBER() OVER (PARTITION BY [Order no.] ORDER BY FBH DESC) AS fbh_rank
    FROM YourTableName -- 替换为实际表名
) ranked_data
PIVOT (
    MAX(FBH) FOR fbh_rank IN ([1], [2], [3], [4], [5], [6])
) pivoted_data
GROUP BY [Order no.], [1], [2], [3], [4], [5], [6];

方案2：动态SQL（自动适配任意数量的FBH列） #

适用于MySQL等不支持PIVOT或需灵活适配列数的场景：

-- 1. 获取单个订单最多的FBH记录数
SET @max_fbh_count = (
    SELECT COUNT(*) 
    FROM YourTableName 
    GROUP BY `Order no.` 
    ORDER BY COUNT(*) DESC 
    LIMIT 1
);

-- 2. 生成动态列的SQL片段
SET @column_sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT('MAX(CASE WHEN fbh_rank = ', num, ' THEN FBH END) AS `FBH-', num, '`')
) INTO @column_sql
FROM (
    SELECT @row := @row + 1 AS num 
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) t,
         (SELECT @row := 0) init
) nums
WHERE num <= @max_fbh_count;

-- 3. 拼接并执行完整SQL
SET @full_sql = CONCAT('
SELECT 
    `Order no.`,
    MAX(`Column 1`) AS `Column 1`,
    MAX(`Column 2`) AS `Column 2`,
    ', @column_sql, '
FROM (
    SELECT 
        `Order no.`,
        `Column 1`,
        `Column 2`,
        FBH,
        ROW_NUMBER() OVER (PARTITION BY `Order no.` ORDER BY FBH DESC) AS fbh_rank
    FROM YourTableName
) ranked_data
GROUP BY `Order no.`');

PREPARE stmt FROM @full_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

关键说明 #

• 替换代码中的YourTableName为实际数据表名称
• 窗口函数ROW_NUMBER()用于给每个订单下的FBH按降序分配序号，确保转置后顺序正确
• 对于同一订单下Column 1和Column 2的多值情况，使用MAX()函数取非空有效值（匹配示例数据的逻辑）

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