目录

新建表的数据

查询每个班级成绩总和并排序

如果只想看有成绩的班级，使用INNER JOIN

如果要添加排名

如果要显示具体的排名（不跳号）

RANK() vs DENSE_RANK()

添加及格率统计

统计各分数段人数

---

新建表的数据

班级表和成绩表

sql">CREATE TABLE class (class_id INT PRIMARY KEY,class_name VARCHAR(50)
);CREATE TABLE score (id INT PRIMARY KEY,class_id INT,student_id INT,score DECIMAL(5,2),FOREIGN KEY (class_id) REFERENCES class(class_id)
);

查询每个班级成绩总和并排序

java">SELECT c.class_id,c.class_name,SUM(s.score) as total_score,COUNT(s.student_id) as student_count,AVG(s.score) as avg_score
FROM class c
LEFT JOIN score s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name
ORDER BY total_score DESC;

如果只想看有成绩的班级，使用INNER JOIN

sql">SELECT c.class_id,c.class_name,SUM(s.score) as total_score,COUNT(s.student_id) as student_count,AVG(s.score) as avg_score
FROM class c
INNER JOIN score s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name
ORDER BY total_score DESC;

如果要添加排名

sql">SELECT class_info.*,RANK() OVER (ORDER BY total_score DESC) as rank
FROM (SELECT c.class_id,c.class_name,SUM(s.score) as total_score,COUNT(s.student_id) as student_count,AVG(s.score) as avg_scoreFROM class cINNER JOIN score s ON c.class_id = s.class_idGROUP BY c.class_id, c.class_name
) class_info;

如果要显示具体的排名（不跳号）

sql">SELECT class_info.*,DENSE_RANK() OVER (ORDER BY total_score DESC) as rank
FROM (SELECT c.class_id,c.class_name,SUM(s.score) as total_score,COUNT(s.student_id) as student_count,AVG(s.score) as avg_scoreFROM class cINNER JOIN score s ON c.class_id = s.class_idGROUP BY c.class_id, c.class_name
) class_info;

RANK() vs DENSE_RANK()

• RANK() 会出现跳号（如1,2,2,4）

• DENSE_RANK() 不会跳号（如1,2,2,3）

添加及格率统计

sql">SELECT class_info.*,ROUND(pass_count * 100.0 / student_count, 2) as pass_rate
FROM (SELECT c.class_id,c.class_name,SUM(s.score) as total_score,COUNT(s.student_id) as student_count,SUM(CASE WHEN s.score >= 60 THEN 1 ELSE 0 END) as pass_countFROM class cINNER JOIN score s ON c.class_id = s.class_idGROUP BY c.class_id, c.class_name
) class_info
ORDER BY total_score DESC;

统计各分数段人数

sql">-- 统计各分数段人数
SELECT c.class_id,c.class_name,SUM(CASE WHEN s.score >= 90 THEN 1 ELSE 0 END) as excellent_count,SUM(CASE WHEN s.score >= 80 AND s.score < 90 THEN 1 ELSE 0 END) as good_count,SUM(CASE WHEN s.score >= 70 AND s.score < 80 THEN 1 ELSE 0 END) as fair_count,SUM(CASE WHEN s.score >= 60 AND s.score < 70 THEN 1 ELSE 0 END) as pass_count,SUM(CASE WHEN s.score < 60 THEN 1 ELSE 0 END) as fail_count
FROM class c
INNER JOIN score s ON c.class_id = s.class_id
GROUP BY c.class_id, c.class_name
ORDER BY c.class_id;