MySQL动态按部门均等抽取上限记录的查询优化需求

针对你遇到的硬编码行数无法适配部门数量变化的问题，我提供两种解决方案，分别对应“尽量平均且总条数不超100”和“简单均分（可能略少于100）”的场景：

方案一：精确平均分配（总条数尽可能接近100） #

这个方案会在部门数量无法整除100时，让前N个部门多取1条（N为余数），确保总条数最多100且分配最均匀：

WITH dept_stats AS (
    -- 统计活跃部门的总数，以及100除以部门数的余数
    SELECT 
        COUNT(DISTINCT department) AS dept_count,
        100 % COUNT(DISTINCT department) AS remainder
    FROM TABLE_NAME
    WHERE status='ACTIVE'
),
ranked_depts AS (
    -- 给每个活跃部门排序，用于确定哪些部门可以多取1条
    SELECT 
        department,
        ROW_NUMBER() OVER(ORDER BY department) AS dept_rank
    FROM (SELECT DISTINCT department FROM TABLE_NAME WHERE status='ACTIVE') d
),
ranked_data AS (
    -- 给每个部门内的活跃记录按id排序，生成部门内的行号
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY t.department ORDER BY t.id) AS dept_entry
    FROM TABLE_NAME t
    WHERE t.status='ACTIVE'
)
-- 最终筛选：根据部门排名和余数计算每个部门的抽取行数，最后限制总条数不超100
SELECT rd.*
FROM ranked_data rd
JOIN ranked_depts rdpt ON rd.department = rdpt.department
JOIN dept_stats ds ON 1=1
WHERE 
    rd.dept_entry <= (
        FLOOR(100 / ds.dept_count) + 
        CASE WHEN rdpt.dept_rank <= ds.remainder THEN 1 ELSE 0 END
    )
ORDER BY rd.department, rd.id
LIMIT 100;

代码解释： #

• dept_stats：先拿到活跃部门的总数，以及100除以部门数后剩下的余数，比如3个部门时余数是1，意味着有1个部门可以多取1条（34条，其他两个33条）。
• ranked_depts：给每个活跃部门按名称排序，这样余数对应的前N个部门就能多分配1条。
• ranked_data：和你原来的子查询逻辑类似，给每个部门内的记录生成行号，方便后续筛选指定行数。
• 最后关联所有CTE，通过CASE判断当前部门是否需要多取1条，再用LIMIT 100兜底，确保总条数不超过限制。

方案二：简单均分（总条数可能略少于100） #

如果不需要严格凑到100条，只需要每个部门抽取行数尽可能相等，这个简化版更易读：

WITH dept_stats AS (
    SELECT COUNT(DISTINCT department) AS dept_count
    FROM TABLE_NAME
    WHERE status='ACTIVE'
),
ranked_data AS (
    SELECT 
        t.*,
        ROW_NUMBER() OVER(PARTITION BY t.department ORDER BY t.id) AS dept_entry
    FROM TABLE_NAME t
    WHERE t.status='ACTIVE'
)
SELECT *
FROM ranked_data
WHERE dept_entry <= FLOOR(100 / (SELECT dept_count FROM dept_stats))
ORDER BY department, id
LIMIT 100;

代码解释： #

直接用FLOOR(100 / dept_count)计算每个部门的抽取行数，比如3个部门时每个取33条，总99条，符合最多100条的要求。如果某个部门的活跃记录数少于计算出的行数，会自动取该部门的全部记录，不会报错。

注意事项： #

• 两个方案都只针对status='ACTIVE'的记录，和你原查询的过滤逻辑一致。
• 如果某个部门的活跃记录数少于计算出的抽取行数，会返回该部门的所有活跃记录，不会强行凑数。

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