如何在SQL Server中创建多分类子项的组合分组结果表

输入表基础信息 #

• 共包含6个字段：CategoryPosition、CategoryId、CategoryName、CategoryItemId、CategoryItemName、CategoryItemPosition
• 覆盖3个分类维度：性别、工时、年龄
• 总记录数共14条：性别分类含3个子项、工时分类含5个子项、年龄分类含6个子项

核心需求 #

对三个分类的所有子项做笛卡尔积全量组合，为每个唯一组合分配不重复的GroupPos编号，最终输出包含GroupPos、全量分类信息、全量子项信息的目标表，总输出记录数为356*3=270条。

参考实现（SQL版本） #

假设输入表表名为category_config，可通过以下逻辑实现需求：

-- 提取三个分类的子项集合
WITH gender_items AS (
    SELECT 
        CategoryPosition AS gender_category_pos,
        CategoryId AS gender_category_id,
        CategoryName AS gender_category_name,
        CategoryItemId AS gender_item_id,
        CategoryItemName AS gender_item_name,
        CategoryItemPosition AS gender_item_pos
    FROM category_config
    WHERE CategoryName = '性别'
),
work_hour_items AS (
    SELECT 
        CategoryPosition AS wh_category_pos,
        CategoryId AS wh_category_id,
        CategoryName AS wh_category_name,
        CategoryItemId AS wh_item_id,
        CategoryItemName AS wh_item_name,
        CategoryItemPosition AS wh_item_pos
    FROM category_config
    WHERE CategoryName = '工时'
),
age_items AS (
    SELECT 
        CategoryPosition AS age_category_pos,
        CategoryId AS age_category_id,
        CategoryName AS age_category_name,
        CategoryItemId AS age_item_id,
        CategoryItemName AS age_item_name,
        CategoryItemPosition AS age_item_pos
    FROM category_config
    WHERE CategoryName = '年龄'
),
-- 生成全量组合并分配唯一GroupPos
group_combinations AS (
    SELECT 
        ROW_NUMBER() OVER(ORDER BY gender_item_pos, wh_item_pos, age_item_pos) AS GroupPos,
        g.*,
        wh.*,
        a.*
    FROM gender_items g
    CROSS JOIN work_hour_items wh
    CROSS JOIN age_items a
)
-- 每个组合拆分为3条记录，对应三个分类维度，得到最终270条输出
SELECT 
    GroupPos,
    CASE t.type 
        WHEN 1 THEN gender_category_pos 
        WHEN 2 THEN wh_category_pos 
        ELSE age_category_pos 
    END AS CategoryPosition,
    CASE t.type 
        WHEN 1 THEN gender_category_id 
        WHEN 2 THEN wh_category_id 
        ELSE age_category_id 
    END AS CategoryId,
    CASE t.type 
        WHEN 1 THEN gender_category_name 
        WHEN 2 THEN wh_category_name 
        ELSE age_category_name 
    END AS CategoryName,
    CASE t.type 
        WHEN 1 THEN gender_item_id 
        WHEN 2 THEN wh_item_id 
        ELSE age_item_id 
    END AS CategoryItemId,
    CASE t.type 
        WHEN 1 THEN gender_item_name 
        WHEN 2 THEN wh_item_name 
        ELSE age_item_name 
    END AS CategoryItemName,
    CASE t.type 
        WHEN 1 THEN gender_item_pos 
        WHEN 2 THEN wh_item_pos 
        ELSE age_item_pos 
    END AS CategoryItemPosition
FROM group_combinations
CROSS JOIN (SELECT 1 AS type UNION ALL SELECT 2 UNION ALL SELECT 3) t

逻辑说明 #

• 先通过CTE分别拆分三个分类的子项数据，避免笛卡尔积时出现脏数据
• 用CROSS JOIN生成356=90个唯一子项组合，通过ROW_NUMBER生成连续不重复的GroupPos编号
• 最后和3行的临时分类标记表做笛卡尔积，将每个组合拆分为3条记录，分别对应性别、工时、年龄三个维度的信息，最终得到90*3=270条符合要求的输出

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