SQL查询：按ID分组将非零值优先填充至前置列

原始数据表结构及数据 #

ID | VALUE | SPEC_ID  
----|-------|--------
 1  |   1   |    9   
 2  |   2   |    9   
 3  |   4   |    9   
 4  |   5   |    9   
 5  |   1   |    8   
 6  |   3   |    8   

需求描述 #

按结果集中的ID字段（对应原始表的SPEC_ID）分组，将每组的非零VALUE值依次填充至COL1至COL5的前置列，剩余列补0，目标结果集如下：

COL1  | COL2  | COL3  | COL4  | COL5  | ID  |
-------|-------|-------|-------|-------|-----|
   1   |   2   |   4   |   5   |   0   |  9  |
   1   |   3   |   0   |   0   |   0   |  8  |

问题说明 #

此前使用以下SQL查询，结果不符合预期：

select max(COL1), max(COL2), max(COL3), max(COL4), max(COL5), ID FROM MY_TABLE GROUP BY ID

正确SQL实现方案 #

核心思路是先通过窗口函数为每组内的VALUE生成排序序号，再通过条件聚合将对应序号的VALUE映射到COL1至COL5，无对应值的列补0。

通用写法（支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server等） #

SELECT
    MAX(CASE WHEN rn = 1 THEN VALUE ELSE 0 END) AS COL1,
    MAX(CASE WHEN rn = 2 THEN VALUE ELSE 0 END) AS COL2,
    MAX(CASE WHEN rn = 3 THEN VALUE ELSE 0 END) AS COL3,
    MAX(CASE WHEN rn = 4 THEN VALUE ELSE 0 END) AS COL4,
    MAX(CASE WHEN rn = 5 THEN VALUE ELSE 0 END) AS COL5,
    SPEC_ID AS ID
FROM (
    SELECT
        VALUE,
        SPEC_ID,
        ROW_NUMBER() OVER (PARTITION BY SPEC_ID ORDER BY ID) AS rn
    FROM MY_TABLE
    WHERE VALUE != 0 -- 若原始数据无零值可省略此过滤条件
) t
GROUP BY SPEC_ID
ORDER BY ID DESC;

关键说明 #

• 子查询中：
  • PARTITION BY SPEC_ID 按目标分组字段拆分数据集
  • ORDER BY ID 保证VALUE按原始表的ID顺序填充到前置列，若需按VALUE大小排序可改为ORDER BY VALUE
  • ROW_NUMBER() 为每组内的VALUE生成连续序号
• 外层查询通过CASE语句结合MAX()聚合，将对应序号的VALUE填充到指定列，无对应序号的列用0补全
• 最终将SPEC_ID重命名为ID，匹配目标结果集的字段名

内容的提问来源于stack exchange，提问作者Hasan Kaan TURAN