数据库常用函数

分组排序

RANK、DENSE_RANK以及ROW_NUMBER区别

RANK、DENSE_RANK以及ROW_NUMBER区别_小妞贼毒的博客-CSDN博客_row_number和rank和dense_rank

条件表达式函数

case when

SQL之CASE WHEN用法详解_涛声依旧叭的博客-CSDN博客_case when

student

1001    张三    18
1002    张四    17
1003    张五    18
1004    张六     19

grade

1    1001    语文    85
2    1001    数学    81
3    1002    英语    75
4    1002    语文    78
5    1002    数学    79
6    1003    数学    90
7    1003    语文    87
8    1003    英语    78
9    1004    英语    90
10    1004    数学    56

#查询所有学生的数学成绩，显示学生姓名name,分数，由高到低
SELECT a.name,b.score FROM student a, grade b WHERE a.id = b.id AND kemu = '数学' ORDER BY score DESC;

#统计每个学生的总成绩,显示字段：姓名，总成绩
SELECT a.NAME,SUM(b.score) as sum_score FROM student a, grade b WHERE a.id = b.id GROUP BY NAME ORDER BY sum_score ASC;

#统计每个学生的总成绩（由于学生可能有重复名字）,学生id，姓名，总成绩
SELECT a.id,a.name,SUM(b.score) as sum_score FROM student a, grade b WHERE a.id = b.id GROUP BY a.id;

#列出各门课程成绩最好的学生，要求显示字段:学号,姓名,科目，成绩
SELECT b.id,a.name,b.kemu,MAX(b.score)FROM student a,grade b where a.id = b.id GROUP BY kemu;

SELECT @@sql_mode;

set session sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

# 课程成绩最好的前两名
SELECT *
FROM
(
	SELECT b.id,a.name,b.kemu,b.score,row_number() over(partition by kemu order by score desc) as rk
	FROM student a,grade b 
	where a.id = b.id
) tmp
WHERE rk <= 2;

#计算每个人的平均成绩，要求显示字段:学号，姓名，平均成绩
SELECT a.id, a.name, AVG(b.score) AS avg_score from student a, grade b WHERE a.id = b.id GROUP BY name;

#计算每个人的成绩，总分数，平均分，
SELECT a.id,a.name,
SUM(CASE WHEN b.kemu = '语文' THEN score ELSE 0 END) AS 语文,
SUM(CASE WHEN b.kemu = '数学' THEN score ELSE 0 END) AS 数学,
SUM(CASE WHEN b.kemu = '英语' THEN score ELSE 0 END) as 英语,SUM(score),AVG(score)
FROM student a, grade b
WHERE a.id = b.id GROUP BY id;

#列出各门课程的平均成绩
SELECT kemu,AVG(score) FROM grade GROUP BY kemu;

# 问题
SET @ranking = 0;
SELECT a.id, a.name, b.kemu,b.score, (@ranking := @ranking + 1) AS 排名
FROM student a, grade b
WHERE a.id = b.id AND kemu = '数学' ORDER BY score DESC;

#列出数学成绩的排名
SELECT *
FROM
(
	SELECT b.id,a.name,b.kemu,b.score,DENSE_RANK()over(partition by kemu order by score desc) AS 排名
	FROM student a,grade b 
	where a.id = b.id
) tmp
WHERE kemu = '数学';

#列出数学成绩前3名的学生
SELECT a.id,a.name,b.kemu,b.score
from student a,grade b
where a.id = b.id AND kemu = '数学'
ORDER BY score DESC
limit 3;

#查询数学成绩第2和第3名的学生
SELECT a.id,a.name,b.kemu,b.score
from student a,grade b
where a.id = b.id AND kemu = '数学'
ORDER BY score DESC
limit 1,2; # 起点为1 查询的数量为2

#查询第3名到后面所有的学生数学成绩
SELECT a.id,a.name,b.kemu,b.score
from student a,grade b
where a.id = b.id AND kemu = '数学'
ORDER BY score ASC
limit 2,1000;

#统计英语课程少于80分的
SELECT a.id,a.name,b.kemu,b.score
from student a,grade b
where a.id = b.id AND kemu = '英语' AND score<80;

#查找每科成绩前2名
SELECT *
FROM(
SELECT a.id,a.name,b.kemu,b.score,ROW_NUMBER() over(PARTITION by kemu ORDER BY score DESC) as pm
from student a,grade b
where a.id = b.id
) c
WHERE pm <= 2;

#查询xuesheng表每门课都大于80分的学生姓名
SELECT NAME
from student a,grade b
where a.id = b.id
GROUP BY NAME
HAVING MIN(score)>80

SELECT NAME
from student WHERE name not in(
SELECT NAME
from student a,grade b
where a.id = b.id
and score<80)