排序order by
1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低
SELECT a.name, b.score
FROM student a, grade b
WHERE a.id = b.id
AND kemu = '数学'
ORDER BY score
DESC
统计总成绩sum
2.统计每个学生的总成绩,显示字段：姓名，总成绩
SELECT a.name, sum(b.score) as sum_score
FROM student a, grade b
WHERE a.id = b.id
GROUP BY name
DESC
查询前3名
列出数学成绩前3名的学生（要求显示字段：学号，姓名, 科目，成绩）
select *
from grade
where kemu = '数学'
order by score
desc
limit 3
先通过limit取出前三条记录，再结合student表查询
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名记录
limit后面如果只写一个整数n，那就是查询的前n条记录；如果后面带2个整数n 和 m，那么第一个数n就是查询出来队列的起点（从0开始），第二个是m是统计的总数目
第2-3条记录，那么起点就是1， 第2-3名有2条记录，那么第二个参数就是2
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
备注：limit是按条数取的，名次一样的，也算一个记录。如果取第5-14的记录，那就是limit 4 10
查询第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, 10000
英语课程少于80分的人
统计英语课程少于80分的，显示 学号id, 姓名，科目，分数
SELECT a.id, a.name, b.kemu, b.score
FROM student a, grade b
WHERE a.id = b.id
AND b.kemu = '英语'
AND b.score < 80
统计每门课程不及格、一般、优秀
|
课程 |
不及格（<60） |
一般（60<= x <=80） |
优秀（>80） |
|
|
|
|
|
SELECT b.kemu,
(SELECT COUNT(*) FROM grade WHERE score < 60 and kemu = b.kemu) as 不及格,
(SELECT COUNT(*) FROM grade WHERE score between 60 and 80 and kemu = b.kemu) as 一般,
(SELECT COUNT(*) FROM grade WHERE score > 80 and kemu = b.kemu) as 优秀
FROM grade b
GROUP BY kemu
查找每科成绩前2名
查找每科成绩前2名，显示id, 姓名，科目，分数
先按科目和分数查询
SELECT t1.id, t1.kemu,t1.score
FROM grade t1
ORDER BY t1.kemu,t1.score DESC
再查找每个每科前面2名
SELECT t1.id, a.name, t1.kemu,t1.score
FROM grade t1, student a
WHERE
(SELECT count(*) FROM grade t2
WHERE t1.kemu=t2.kemu AND t2.score>=t1.score
)<=2
and a.id = t1.id
ORDER BY t1.kemu,t1.score
DESC
计算学生平均分数
计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩
select a.id, a.name, c.avg_score
from student a,
(select b.id, avg(b.score) as avg_score
from grade b
group by b.id
)c
where a.id = c.id
统计各科目成绩
计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分
使用case when 语法把科目字段分解成具体的科目：语文，数学， 英语
select a.id as 学号, a.name as 姓名,
(case when b.kemu='语文' then score else 0 end) as 语文,
(case when b.kemu='数学' then score else 0 end) as 数学,
(case when b.kemu='英语' then score else 0 end) as 英语
from student a, grade b
where a.id = b.id
SELECT a.id as 学号, a.name as 姓名,
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(b.score) as 总分 ,
sum(b.score)/count(b.score) as 平均分
FROM student a, grade b
where a.id = b.id
GROUP BY b.id, b.id
每门课程平均成绩
列出各门课程的平均成绩，要求显示字段：课程，平均成绩
select b.kemu, avg(b.score)
from grade b
group by b.kemu
成绩排名
列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名
在查询结果表里面添加一个变量@paiming,让它自动加1
SELECT
t.id, t.score as 数学分数, @paiming := @paiming+1 as 排名
FROM
(SELECT b.id, b.score
FROM grade b
WHERE b.kemu = '数学'
ORDER BY score
DESC) AS t,
(SELECT @paiming := 0) r
结合student表获取学生名称
SELECT
t.id, a.name,t.score as 数学分数, @paiming := @paiming+1 as 排名
FROM
(SELECT b.id, b.score
FROM grade b
WHERE b.kemu = '数学'
ORDER BY score
DESC) AS t,
(SELECT @paiming := 0) r,
student a
WHERE a.id = t.id
同结果名次相同
上图由于同一个分数的小伙伴，排名不一样，本着公平、公正、公开的原则，同一分数名次一样
SELECT
t.id, a.name,t.score as 数学分数,
(CASE
WHEN @temp = t.score THEN
@paiming
WHEN @temp := t.score THEN
@paiming :=@paiming + 1
WHEN @temp = 0 THEN
@paiming :=@paiming + 1
END) AS num
FROM
(SELECT b.id, b.score
FROM grade b
WHERE b.kemu = '数学'
ORDER BY score
DESC) AS t,
(SELECT @paiming := 0, @temp := 0) r,
student a
WHERE a.id = t.id
排名相同的占个名次
SELECT obj.id, obj.score as 数学,
@rownum := @rownum + 1 AS num_tmp,
@incrnum := (CASE
WHEN @rowtotal = obj.score THEN
@incrnum
WHEN @rowtotal := obj.score THEN
@rownum
END) AS 排名
FROM
(SELECT id, score
FROM grade
WHERE kemu = "数学"
ORDER BY
score DESC
) AS obj,
(SELECT @rownum := 0 ,@rowtotal := NULL ,@incrnum := 0) r
posted on
