如何在SQL Server中按行关联分类与对应产品？

核心思路 #

由于ID是严格递增的，每个分类行之后的产品都隶属于该分类，我们可以利用窗口函数，为每个产品行匹配最近的上一个分类行，从而建立分类与产品的关联关系，同时处理原始数据里的名称修正问题。

具体SQL实现 #

假设你的原始表名为ProductList，包含Item（存储分类/产品名称）和ID两列，执行以下SQL即可得到目标格式：

WITH CategorizedItems AS (
    -- 标记分类行，并为每个行匹配最近的上一个分类名称
    SELECT 
        Item,
        ID,
        -- 筛选分类行，用MAX()窗口函数向前填充分类名称到后续产品行
        MAX(CASE WHEN Item LIKE 'Category-%' THEN Item END) OVER (ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Category
    FROM ProductList
),
CorrectedData AS (
    SELECT
        -- 修正分类名称：替换指定分类的命名
        CASE 
            WHEN Category = 'Category-Phone Accessories' THEN 'Category-Mobile Phone Accessories'
            ELSE Category
        END AS Categories,
        -- 修正产品名称的截断与格式问题
        CASE Item
            WHEN 'Garmin Venu 3 with Silicone B.' THEN 'Garmin Venu 3 with Silicone Band'
            WHEN 'Tile Mate Essential (2022) 4P.' THEN 'Tile Mate Essential (2022) 4-Pack'
            WHEN 'Apple Clear Case iPhone 11Pro' THEN 'Apple Clear Case for iPhone 11Pro'
            ELSE Item
        END AS Products
    FROM CategorizedItems
    -- 过滤掉分类行本身，只保留产品数据
    WHERE Item NOT LIKE 'Category-%'
)
SELECT Categories, Products
FROM CorrectedData
ORDER BY ID;

代码说明 #

1. CategorizedItems CTE：先用CASE语句识别分类行，再通过MAX() OVER(ORDER BY ID ...)窗口函数，将分类名称向下填充到后续所有产品行，让每个产品都关联到所属分类。
2. CorrectedData CTE：处理原始数据里的名称瑕疵，包括调整分类命名、补全/修正产品名称的截断内容。
3. 最后过滤掉分类行，仅保留产品与分类的关联数据，按ID排序输出结果。

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