SQL Server：根据条件获取对应主类别值的问题求助

问题场景 #

现有表包含OrderNo、CategoryType、CategoryName三列：

• CategoryType为空值时，该行是主类别
• CategoryType为'Sub Category'时，该行是子类别

需求是新增Main_Category_Name列，为每个子类别行填充按OrderNo排序后，其上方最近的主类别名称。LAG()函数仅能获取上一行值，无法满足跨多行匹配最近主类别的需求。

解决方案：使用窗口函数分组匹配 #

核心思路是先将每个主类别及其下方的子类别划分为同一分组，再在分组内提取主类别的名称。以下是具体实现步骤：

1. 构造测试数据

-- 创建测试表并插入数据
WITH TestData AS (
    SELECT 1 AS OrderNo, NULL AS CategoryType, '电子产品' AS CategoryName UNION ALL
    SELECT 2 AS OrderNo, 'Sub Category' AS CategoryType, '手机' AS CategoryName UNION ALL
    SELECT 3 AS OrderNo, 'Sub Category' AS CategoryType, '电脑' AS CategoryName UNION ALL
    SELECT 4 AS OrderNo, NULL AS CategoryType, '家居用品' AS CategoryName UNION ALL
    SELECT 5 AS OrderNo, 'Sub Category' AS CategoryType, '沙发' AS CategoryName UNION ALL
    SELECT 6 AS OrderNo, 'Sub Category' AS CategoryType, '床' AS CategoryName UNION ALL
    SELECT 7 AS OrderNo, NULL AS CategoryType, '食品' AS CategoryName UNION ALL
    SELECT 8 AS OrderNo, 'Sub Category' AS CategoryType, '零食' AS CategoryName
)

2. 生成分组ID并匹配主类别名称

SELECT
    OrderNo,
    CategoryType,
    CategoryName,
    -- 提取分组内的主类别名称
    MAX(CASE WHEN CategoryType IS NULL THEN CategoryName END) OVER (PARTITION BY GroupID) AS Main_Category_Name
FROM (
    SELECT
        *,
        -- 生成分组ID：遇到主类别时累加，子类别继承当前分组ID
        SUM(CASE WHEN CategoryType IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY OrderNo) AS GroupID
    FROM TestData
) t
ORDER BY OrderNo;

执行结果 #

代码解释 #

1. 分组ID生成：SUM(CASE WHEN CategoryType IS NULL THEN 1 ELSE 0 END) OVER (ORDER BY OrderNo)
   • 按OrderNo排序，每遇到一个主类别（CategoryType为空），分组ID加1；子类别行保持当前分组ID不变，实现主类别与下方子类别的分组关联。
2. 提取主类别名称：MAX(CASE WHEN CategoryType IS NULL THEN CategoryName END) OVER (PARTITION BY GroupID)
   • 在每个分组内，筛选出主类别的CategoryName，用MAX()聚合确保只取到唯一的主类别名称（每个分组仅有一个主类别）。

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