如何按州分组查询Top2高温地点？基于max列的分组前N行实现

数据表示例 #

解决方案 #

针对不同数据库，以下是实现查询指定任意数量州中每个州Top2高温地点的SQL语句：

MySQL #

利用用户变量实现分组内排序：

SELECT id, state, place, temp
FROM (
    SELECT 
        id, state, place, temp,
        @rank := IF(@current_state = state, @rank + 1, 1) AS rank,
        @current_state := state
    FROM your_table
    WHERE state IN ('AA', 'EE') -- 替换为你需要查询的州列表
    ORDER BY state, temp DESC
) ranked_results
WHERE rank <= 2;

PostgreSQL / SQL Server #

使用窗口函数ROW_NUMBER()更简洁：

SELECT id, state, place, temp
FROM (
    SELECT 
        id, state, place, temp,
        ROW_NUMBER() OVER (PARTITION BY state ORDER BY temp DESC) AS rank
    FROM your_table
    WHERE state IN ('AA', 'EE') -- 动态指定目标州
) ranked_results
WHERE rank <= 2;

注意事项 #

• 将your_table替换为实际表名
• state IN (...)中的州列表可根据需求调整（2个、5个或更多都适用）
• 若要保留温度并列的地点，可将ROW_NUMBER()替换为RANK()或DENSE_RANK()：
  • RANK()：并列的记录会占用相同排名，后续排名会跳过对应的位数
  • DENSE_RANK()：并列的记录占用相同排名，后续排名连续递增

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