mysql 查询每组第二名

CREATE TABLE scores (
    id INT PRIMARY KEY,
    group_name VARCHAR(50),
    score INT
);

INSERT INTO scores (id, group_name, score) VALUES
(1, 'GroupA', 80),
(2, 'GroupA', 90),
(3, 'GroupA', 70),
(4, 'GroupB', 85),
(5, 'GroupB', 95),
(6, 'GroupB', 80);

SELECT s1.*
FROM scores s1
JOIN (
    SELECT group_name, MAX(score) AS second_highest_score
    FROM scores s2
    WHERE s2.score < (
        SELECT MAX(score)
        FROM scores s3
        WHERE s3.group_name = s2.group_name
    )
    GROUP BY group_name
) s4 ON s1.group_name = s4.group_name AND s1.score = s4.second_highest_score;

WITH ranked_scores AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY group_name ORDER BY score DESC) AS rn
    FROM scores
)
SELECT *
FROM ranked_scores
WHERE rn = 2;