SQL查询如何按条件筛选行，保留指定列分组内RANK值最高的记录

需求适配说明 #

不需要固定写死rank=5的筛选条件，方案会自动识别每个market_id分组内的最高rank值，仅保留对应记录，适配部分分组最高rank不足5的场景。

注：rank属于部分数据库的SQL保留关键字，如果执行报错可将字段名用反引号包裹，即`rank`。

方案1：窗口函数实现（推荐，兼容MySQL8.0+、PostgreSQL、SQL Server、Oracle等主流数据库） #

WITH filtered_data AS (
    SELECT 
        *,
        -- 按market_id分组计算每个组内的最大rank值
        MAX(rank) OVER (PARTITION BY market_id) AS group_max_rank
    FROM XXXX
    WHERE 
        Segment = 'Gewerbe' 
        AND Division = 'Strom' 
        AND consumption >= 1000 
        AND consumption < 2000 
        AND rank <= 5 
        AND market_id = '39a2e05fd43300c998558ef56bca18e2'
)
SELECT * 
FROM filtered_data 
WHERE rank = group_max_rank
ORDER BY consumption, rank;

方案2：子查询关联实现（兼容不支持窗口函数的低版本数据库，如MySQL5.7及以下） #

SELECT t1.*
FROM XXXX t1
-- 关联子查询拿到每个分组的最大rank值
INNER JOIN (
    SELECT market_id, MAX(rank) AS group_max_rank
    FROM XXXX
    WHERE 
        Segment = 'Gewerbe' 
        AND Division = 'Strom' 
        AND consumption >= 1000 
        AND consumption < 2000 
        AND rank <= 5 
        AND market_id = '39a2e05fd43300c998558ef56bca18e2'
    GROUP BY market_id
) t2 ON t1.market_id = t2.market_id AND t1.rank = t2.group_max_rank
WHERE 
    t1.Segment = 'Gewerbe' 
    AND t1.Division = 'Strom' 
    AND t1.consumption >= 1000 
    AND t1.consumption < 2000 
    AND t1.rank <= 5 
    AND t1.market_id = '39a2e05fd43300c998558ef56bca18e2'
ORDER BY t1.consumption, t1.rank;

自定义调整说明 #

如果你的分组维度除market_id外还有其他字段，只需在窗口函数的PARTITION BY子句、或子查询的GROUP BY子句中追加对应字段即可。

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