mysql根据分数排名

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

-- 插入示例数据
INSERT INTO scores (student_name, score) VALUES
('Alice', 85),
('Bob', 92),
('Charlie', 78),
('David', 92);

-- 使用ROW_NUMBER()进行排名
SELECT student_name, score, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank
FROM scores;

-- 使用RANK()进行排名
SELECT student_name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

-- 使用DENSE_RANK()进行排名
SELECT student_name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

-- 使用子查询进行排名
SELECT s1.student_name, s1.score,
    (SELECT COUNT(DISTINCT s2.score)
     FROM scores s2
     WHERE s2.score > s1.score) + 1 AS rank
FROM scores s1
ORDER BY score DESC;

SELECT student_name, score, DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;