SQL实现按mandal_id分组每组强制返回30条唯一记录的高效写法

按mandal_id分组查询父表ds的记录，每组强制返回30条，优先选取符合ds_details.status = 0且ds.assigned_to_user IS NULL的唯一记录，不足部分用其余合法关联的记录补充。

• 父表 ds 字段：id, survey_id, village_id, mandal_id, district_id, status, assigned_to_user, created_at
• 子表 ds_details 字段：id, ds_id（关联父表ds.id的外键）, sub_survey_id, crop_id, crop_variety_id

原有查询仅筛选满足ds_details.status = 0 AND ds.assigned_to_user IS NULL的关联记录，当单个mandal_id分组下符合该条件的去重父表记录不足30条时，无法凑够每组30条的要求。

1. 给记录设置优先级：
   • 优先级1：满足原筛选条件的去重父表记录，优先选取
   • 优先级2：其余合法关联的父表记录，用来补充不足30条的缺口
2. 按mandal_id分组后，先按优先级升序、再按创建时间升序排序，取每组前30条即可保证每组固定返回30条
3. 提前对父表记录去重，避免同一个父表ID因为关联多个子表重复出现

WITH all_valid_ds AS (
    -- 拿到所有去重的父表记录，标记优先级
    SELECT DISTINCT
        d.id,
        d.survey_id,
        d.village_id,
        d.mandal_id,
        d.created_at,
        CASE WHEN dd.status = 0 AND d.assigned_to_user IS NULL THEN 1 ELSE 2 END AS priority
    FROM ds d
    INNER JOIN ds_details dd ON d.id = dd.ds_id
),
ranked_ds AS (
    -- 按mandal分组排序
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY mandal_id ORDER BY priority ASC, created_at ASC) AS group_rn
    FROM all_valid_ds
)
-- 取每组前30条
SELECT id, survey_id, village_id, mandal_id
FROM ranked_ds
WHERE group_rn <= 30
ORDER BY mandal_id, group_rn;

• 给ds_details.ds_id、ds.mandal_id字段添加索引，大幅提升关联和分组的查询效率
• 如果有额外的通用过滤规则（比如父表状态必须为有效），可以在all_valid_ds的WHERE子句中提前过滤，减少计算量
• 极端场景下如果整个分组的所有合法记录都不足30条，可通过递归CTE生成缺额的虚拟记录，按需填充默认值即可

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