如何在MySQL中查找数据表每行特定列的第二大值

针对你这个需求，我整理了两种实用的方法，分别适合不同的场景，咱们来详细看看：

方法一：适合固定少量玩家列（MySQL 5.x及以上通用） #

如果你的玩家列数量固定（比如你提到的4列），且不需要频繁新增列，可以用嵌套的GREATEST()和LEAST()函数组合来计算。因为你明确说没有重复值，这个方法能直接得到第二大的非NULL值：

SELECT 
    Game_no,
    CASE
        -- 当该行非NULL的分数不足2个时，返回NULL（可根据实际需求调整）
        WHEN (Player_1 IS NOT NULL) + (Player_2 IS NOT NULL) + (Player_3 IS NOT NULL) + (Player_4 IS NOT NULL) < 2 THEN NULL
        ELSE GREATEST(
            LEAST(GREATEST(Player_1, Player_2), GREATEST(Player_3, Player_4)),
            LEAST(GREATEST(Player_1, Player_3), GREATEST(Player_2, Player_4)),
            LEAST(GREATEST(Player_1, Player_4), GREATEST(Player_2, Player_3))
        )
    END AS Second_place_score
FROM your_game_table;

原理说明： #

通过两两组合取最大值，再取每组的最小值，最后在这些最小值里取最大的，就是整行的第二大值。这个方法不需要额外的表操作，性能较好，但列数多了之后写法会很繁琐。

方法二：通用方法（MySQL 8.0及以上） #

如果玩家列数量较多，或者未来可能新增列，推荐用行转列+窗口函数的方式，扩展性更强：

-- 先把每行的玩家分数拆成多行
WITH game_scores AS (
    SELECT Game_no, Player_1 AS score FROM your_game_table WHERE Player_1 IS NOT NULL
    UNION ALL
    SELECT Game_no, Player_2 AS score FROM your_game_table WHERE Player_2 IS NOT NULL
    UNION ALL
    SELECT Game_no, Player_3 AS score FROM your_game_table WHERE Player_3 IS NOT NULL
    UNION ALL
    SELECT Game_no, Player_4 AS score FROM your_game_table WHERE Player_4 IS NOT NULL
),
-- 按游戏分组，给分数降序排名
ranked_scores AS (
    SELECT 
        Game_no,
        score,
        ROW_NUMBER() OVER (PARTITION BY Game_no ORDER BY score DESC) AS rank_num
    FROM game_scores
)
-- 取排名第2的分数
SELECT 
    Game_no,
    score AS Second_place_score
FROM ranked_scores
WHERE rank_num = 2;

原理说明： #

1. 用UNION ALL把每行的非NULL玩家分数拆成单独的行，让每一行对应一个游戏的一个玩家分数；
2. 用ROW_NUMBER()窗口函数按游戏分组，分数从高到低排名；
3. 最后筛选出排名为2的记录，就是该行的第二大值。

如果某行只有1个非NULL分数，这个方法不会返回对应的行，你可以根据需求调整（比如用LEFT JOIN关联原表来保留所有Game_no，没有第二大值时显示NULL）。

内容的提问来源于stack exchange，提问作者Rand al'Thor