基于Supergroup与Subgroup分配取数至40条的SQL需求

这个需求的核心是按supergroup的subgroup数量比例分配取数名额，最后补全到总40条，我用SQL Server的CTE（公共表表达式）来实现，逻辑清晰且容易维护。

实现步骤拆解 #

• 第一步：统计每个supergroup下的distinct subgroup数量，同时计算所有supergroup的subgroup总数
• 第二步：计算基础分配条数（总40条除以总subgroup数的整数部分），以及剩余需要补的条数（总40条对总subgroup数取余）
• 第三步：给每个supergroup分配基础条数，前N个supergroup（N=剩余条数）额外多1条，确保总条数刚好40
• 第四步：给每个supergroup下的记录排序，取对应分配条数的记录标记flag='Y'，其余标记flag='N'

完整SQL代码 #

-- 定义测试表（你可以替换成实际表）
declare @tbl table (id int identity ,supergroup int ,subgroup int , flag char(1) )

-- 插入测试数据（模拟不同supergroup和subgroup的情况）
insert into @tbl(supergroup, subgroup)
values 
(1000, 1), (1000,1), (1000,2), (1000,2), (1000,3), (1000,3),
(2000,4), (2000,4), (2000,5), (2000,5), (2000,6), (2000,6),
(3000,7), (3000,7), (3000,8), (3000,8), (3000,9), (3000,9),
-- 多插一些数据确保能取到40条
(1000,1), (1000,2), (1000,3), (2000,4), (2000,5), (2000,6),
(3000,7), (3000,8), (3000,9), (1000,1), (1000,2), (1000,3),
(2000,4), (2000,5), (2000,6), (3000,7), (3000,8), (3000,9),
(1000,1), (1000,2), (1000,3), (2000,4), (2000,5), (2000,6),
(3000,7), (3000,8), (3000,9), (1000,1), (1000,2), (1000,3)

;WITH SuperSubStats AS (
    -- 统计每个supergroup的distinct subgroup数量
    SELECT 
        supergroup,
        COUNT(DISTINCT subgroup) AS sub_count
    FROM @tbl
    GROUP BY supergroup
),
TotalStats AS (
    -- 计算总subgroup数、基础分配数、剩余条数
    SELECT 
        SUM(sub_count) AS total_subs,
        40 / SUM(sub_count) AS base_quota,
        40 % SUM(sub_count) AS remaining_quota
    FROM SuperSubStats
),
SuperQuota AS (
    -- 给每个supergroup分配最终名额：基础配额 + 前N个多1条（N=剩余条数）
    SELECT 
        s.supergroup,
        t.base_quota + CASE WHEN ROW_NUMBER() OVER (ORDER BY s.supergroup) <= t.remaining_quota THEN 1 ELSE 0 END AS final_quota
    FROM SuperSubStats s
    CROSS JOIN TotalStats t
),
RankedRecords AS (
    -- 给每个supergroup下的记录按id排序（你可以换成其他排序字段，比如subgroup）
    SELECT 
        id,
        supergroup,
        ROW_NUMBER() OVER (PARTITION BY supergroup ORDER BY id) AS record_rank
    FROM @tbl
)
-- 更新flag列：符合配额的标记为Y，其余为N
UPDATE t
SET flag = CASE WHEN r.record_rank <= sq.final_quota THEN 'Y' ELSE 'N' END
FROM @tbl t
JOIN RankedRecords r ON t.id = r.id
JOIN SuperQuota sq ON r.supergroup = sq.supergroup

-- 验证结果
SELECT supergroup, subgroup, flag, COUNT(*) AS count_per_group
FROM @tbl
GROUP BY supergroup, subgroup, flag
ORDER BY supergroup, subgroup, flag

关键说明 #

• 排序逻辑：代码里用ORDER BY id来给每个supergroup下的记录排序，你可以根据实际需求换成其他字段（比如subgroup或者时间字段）
• 配额计算：如果总subgroup数能整除40，那每个supergroup的配额就是40/总subs；如果有余数，前N个supergroup（按supergroup排序）会多1条，确保总条数刚好40
• 测试数据：我插入了足够多的测试记录，你可以替换成你的实际表，只需要把@tbl换成你的表名即可

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