SQL查询优化：按city/name分组优先筛选高优先级行

数据表 #

需求说明 #

• 从表中获取指定数量的记录，每个city/name组合仅返回一条
• 优先返回所有priority = 1的行（每个组合仅一条）
• 当priority = 1的行数量不足指定条数时，补充priority = 0的行，且选这些行中updated最早的
• 示例：取2条时返回id=1、3；取3条时额外包含id=2

原SQL的问题 #

你尝试的SQL：

select any_value(id) keep(dense_rank first order by priority, updated) from table group by city, name fetch next 2 rows only

问题在于：分组后仅确保了每个city/name组内选到最优行，但没有做全局的优先级排序——直接fetch next可能会把priority=0的行提前选中，无法保证优先取完所有priority=1的组。

优化解决方案 #

使用窗口函数实现一次扫描、逻辑清晰的高效查询：

WITH ranked_groups AS (
    SELECT 
        id,
        name,
        city,
        priority,
        updated,
        -- 每个city/name组内筛选最优行：优先priority=1，再取最早更新的
        ROW_NUMBER() OVER (PARTITION BY city, name ORDER BY priority DESC, updated ASC) AS group_rank,
        -- 标记全局优先级：priority=1的组排在最前面
        CASE WHEN priority = 1 THEN 0 ELSE 1 END AS global_sort_key
    FROM your_table -- 替换为你的实际表名
)
SELECT id, name, city, priority, updated
FROM ranked_groups
WHERE group_rank = 1 -- 每个组合仅保留最优行
ORDER BY global_sort_key ASC, updated ASC -- 先排优先级1的组，再按更新时间排优先级0的组
FETCH NEXT 2 ROWS ONLY; -- 替换为你需要的记录数量

方案优势 #

• 仅需一次表扫描，性能远优于拆分查询再合并的方案，适合大数据量场景扩展
• 逻辑分层清晰：先处理组内最优行，再全局按优先级排序，规则调整灵活
• 兼容绝大多数支持窗口函数的数据库（Oracle、PostgreSQL、MySQL 8.0+、SQL Server等）

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