mysql查询表中第2大值

SELECT MAX(score) AS second_highest_score
FROM scores
WHERE score < (SELECT MAX(score) FROM scores);

SELECT score AS second_highest_score
FROM scores
ORDER BY score DESC
LIMIT 1 OFFSET 1;

SELECT DISTINCT score AS second_highest_score
FROM (
    SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
) ranked_scores
WHERE rank = 2;

-- 创建示例表
CREATE TABLE scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    score INT
);

-- 插入示例数据
INSERT INTO scores (score) VALUES (90), (85), (95), (88), (92);

-- 查询第2大值
SELECT MAX(score) AS second_highest_score
FROM scores
WHERE score < (SELECT MAX(score) FROM scores);