如何编写SQL查询实现商品与对应类别分栏并填充类别空值？

原表 goodsandcat 结构 #

其中Item列存储类别与商品名称，Key列中0代表类别，1代表商品。需求是将类别与商品分别提取到Category和Goods两列，且每个商品行需对应其所属类别。

尝试的SQL语句 #

SELECT 
    CASE WHEN Key = 0 THEN Item ELSE NULL END AS Category,
    CASE WHEN Key = 1 THEN Item ELSE NULL END AS Goods
FROM goodsandcat;

当前查询结果 #

期望结果 #

解决方案 #

可以通过窗口函数追踪最近的类别值，填充到对应商品行中，以下是两种实用实现：

方式一：直接填充最近类别（适用于MySQL 8.0+、PostgreSQL、SQL Server）

SELECT
    MAX(CASE WHEN `Key` = 0 THEN Item END) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Category,
    CASE WHEN `Key` = 1 THEN Item ELSE NULL END AS Goods
FROM goodsandcat;

注：如果表中有自增ID或固定排序字段，建议将(SELECT 1)替换为该字段（比如id），保证行顺序稳定。

方式二：分组后填充（兼容性更强）

先给每个类别及其下属商品分配同一分组ID，再基于分组填充类别：

WITH grouped_data AS (
    SELECT
        *,
        SUM(CASE WHEN `Key` = 0 THEN 1 ELSE 0 END) OVER (ORDER BY (SELECT 1)) AS group_id
    FROM goodsandcat
)
SELECT
    MAX(CASE WHEN `Key` = 0 THEN Item END) OVER (PARTITION BY group_id) AS Category,
    CASE WHEN `Key` = 1 THEN Item ELSE NULL END AS Goods
FROM grouped_data;

原理说明 #

• 第一种方式通过MAX() OVER (...)窗口函数，从当前行向上取所有行中的类别值（非NULL），由于每个分组内只有一个类别值，取最大值即可得到当前商品所属的类别。
• 第二种方式先通过累积求和生成分组ID，把同一类别下的所有商品归为一组，再在组内统一填充类别值。

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