SQL实现按分组取第二高值 无第二高则返回最高值的方法

问题原因 #

你原有SQL的WHERE条件会直接过滤掉只有单一supply_count取值的店铺（比如shopID=1仅存在12这一个供应量，排除最高值12后无剩余记录，所以不会出现在分组结果中），导致这类店铺结果缺失。

解决方案 #

方案1：全版本兼容写法 #

兼容所有主流SQL数据库（含低版本MySQL、SQLite等），基于原有逻辑改造：

SELECT 
    t.shopId,
    COALESCE(s.second_count, t.max_count) AS supply_count
FROM (
    -- 先查询所有店铺的最高供应量，拿到完整的shopID全集
    SELECT shopId, MAX(supply_count) AS max_count
    FROM supply
    GROUP BY shopId
) t
LEFT JOIN (
    -- 原有逻辑查询存在第二高值的店铺的第二高供应量
    SELECT 
        s1.shopId, 
        MAX(s1.supply_count) AS second_count
    FROM supply s1
    WHERE supply_count NOT IN (
        SELECT MAX(supply_count) 
        FROM supply s2
        WHERE s1.shopId = s2.shopId
    )
    GROUP BY s1.shopId
) s ON t.shopId = s.shopId;

方案2：窗口函数写法 #

适合MySQL8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库，逻辑更清晰易维护：

WITH ranked_supply AS (
    SELECT 
        shopId,
        supply_count,
        -- 同店铺按供应量降序排名，相同供应量排名一致
        DENSE_RANK() OVER (PARTITION BY shopId ORDER BY supply_count DESC) AS rk
    FROM supply
    GROUP BY shopId, supply_count -- 按店铺+供应量去重，避免重复值干扰排名
)
SELECT 
    shopId,
    -- 优先取排名第二的值，不存在则取排名第一的最高值
    COALESCE(
        MAX(CASE WHEN rk = 2 THEN supply_count END),
        MAX(CASE WHEN rk = 1 THEN supply_count END)
    ) AS supply_count
FROM ranked_supply
GROUP BY shopId;

两种方案执行后都可以得到你期望的输出结果。

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