查询成绩最好的前两名_SQL查询整理

SQL查询相关知识整理

习题中会用到的表

一、简单查询

1.查询姓“猴”的学生名单

select 学号，姓名
from student 
where 姓名 like ‘猴%’；

2、查询姓名中最后一个字是“猴”的学生名单

select 学号，姓名
from student 
where 姓名 like ‘%猴’；

3、查询姓名中带“猴”的学生名单

select 学号，姓名
from student
where 姓名 like ‘%猴%’；

4、查询姓“孟”老师的个数

select count （教师号）
from teacher
where 教师姓名 like ‘孟%’；

二、汇总分析

1、查询课程编号为“0002”的总成绩

select sum（成绩）
from score
group by 课程号
having 课程号=‘0002’；

2、查询选了课程的学生人数

/*查询有多少人选修了课程，select 学号
成绩表里学号有重复值需要去掉
from 从课程表查询 score
/*
select sum（distinct 学号）as 学生人数
from score

3、查询各科成绩最高和最低的分

select max（成绩）as 最高分 ，min（成绩）as最低分
from score 
group by 课程号；

4、查询每门课程被选修的学生数

select 课程号，count（学号）as学生人数
from score 
group by 课程号；

5、查询男生、女生人数

/*select 查询结果：性别，对应性别人数：汇总函数count
from 从哪张表查 student
where 查询条件：没有
group by 按性别分组
order by 没有排序
/*
select 性别，count（*）
from student
group by 性别；

6、查询平均成绩大于60分学生的学号和平均成绩

select 学号，avg（成绩）
from score
group by 学号
having avg（成绩）>60;

7、查询至少选修两门课程的学生学号

select 学号，count（课程号）as选修课程数
from score
group by 学号
having count（课程号）>=2；

8、查询同名同姓学生名单并统计同名人数

/*翻译：
查询出姓名相同的学生有谁，每个姓名相同学生的人数
查询结果：姓名，人数
条件：怎么算姓名相同？按姓名分组后人数大于等于2，因为同名的人数大于等于2
*/
select 姓名 ，count（*）as 人数
from student
group by 姓名
having count（*）>=2;

9、查询不及格的课程并按课程号从大到小排序

select 课程号
from score
where 成绩<60
order by  课程号 desc；

10、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select 课程号，avg（成绩）as平均成绩
from score
group by 课程号
order by 平均成绩 asc，课程号 desc；

11、检索课程号为‘0004’且分数小于60的学生学号，结果按分数降序排序

select 学号
from score
where 课程号=“0004”and 分数 <60；
order by 成绩 desc；

12、统计每门课程的学生选修人数（超过2人的课程才统计）

要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select 课程号，count（学号）as学生人数
from score
group by 课程号
having count（学号）>2
order by count（学号） desc，课程号 asc；

13、查询两门以上不及格课程的同学的学号及其平均成绩

select 学号，avg（成绩），count（成绩）as不及格科目数
from score
where 成绩 <60
group by 学号
having count（成绩）>=2；

三、复杂查询

1、（子查询）查询所有课程成绩小于60分学生的学号、姓名

select 学号，姓名
from student 
where 学号 in（
select 学号
from score
group by 学号
having max（成绩）<60
）；

2、查询没有学全所有课的学生的学号、姓名

select 学号，姓名
from score
where 学号 in（
select 学号
from score
group by 学号
having count（课程号）<(selece count(课程号）from course）
);

3、查询出只选修了两门课程的全部学生的学号和姓名

select 学号，姓名
from score
where 学号 in （select 学号
from score
group by 学号
having count（课程号）=2
）；

4、查询1990年出生的学生名单

select 学号
from student
where year（出生日期）=1990；

5、按课程号分组取成绩最大值所在行的数据

select * 
from score as a
where 成绩=（
select max（成绩）
from score as b
where a.课程号=b.课程号
）；

6、按课程号分组去成绩最小值所在行的数据

select * 
from score as a
where 成绩=（
select min（成绩）
from score as b
where a.课程号=b.课程号
）；

7、查询各科成绩前两名的记录

(select *
from score 
where 课程号 = ‘0001’
order by 成绩 desc 
limit 2）
union all 
（select * from score where 课程号 =‘0002’order by 成绩 desc limit 2）
union all
（select * from score where 课程号 = ‘0003’order by 成绩 desc limit 2）

四、多表查询

1、查询所有学生的学号、姓名、选课数、总成绩

select a.学号，a.姓名，count（b.课程号）as选课数，sum（b.成绩）as 总成绩
from student as a inner join score as b 
on a.学号= b.学号
group by  a.学号；

2、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select a.学号，a.姓名 ，avg（b.成绩）as平均成绩
from student as a left join score as b
on a. 学号= b.学号
group by a.学号
having avg（b.成绩）>85;

3、查询学生的选课情况：学号，姓名，课程号，课程名称

select a.学号，a.姓名，c.课程号，c.课程名称
from student as a inner join score as b 
on a.学号=b.学号
inner join course as c
on b.课程号=c.课程号；

4、查询出每门课程的及格人数和不及格人数

select 课程号，
sum（case 成绩>=60 then 1
       else 0
end）as及格人数
sum（case 成绩 <60 then 1
       else 
end）as 不及格人数
from score
group by 课程号；

4、使用分段【100-85】、【85-70】、【70-60】、【<60】来统计各科成绩，分别统计：各分数段人数、课程号和课程名称

select a.课程号，b.课程名称，
sum（case when 成绩 between 100 and 85 then 1
else 0
end ）as'100-85人数'
sum (case when 成绩 <85 and 成绩>=70 then 1
else 0
end)as '85-70人数'
sum(case when 成绩<70 and 成绩>=60 then 1
else 0
end）as '70-60人数'
sum(case when 成绩<60 then 1
else 0
end) as '<60人数'
from score as a right join course as b
on a.课程号=b.课程号
group by a.课程号，b.课程名称；

5、查询课程编号为0003且课程成绩在80分以上的学生的学号和姓名

select a.姓名，a.学号

from student as a inner join score as b

on a.学号=b.学号

where b.课程号=‘0003’and b.成绩>80；

6、

这类题目属于行列互换，解题思路如下：

1）用常量列输出目标表的结构，可以看到查询结果已经和目标很接近

select 学号，‘课程号0001’，‘课程号0002’，‘课程号0003’

from score；

2）使用case表达式，替换常量列为对应成绩

select 学号，（
case 课程号 when‘0001’then 成绩 else 0 end）as ‘课程号0001’，
（case 课程号 when‘0002’then 成绩 else 0 end）as ‘课程号0002’，
（case 课程号 when‘0003’then 成绩 else 0 end）as ‘课程号0003’
from score；

这个查询结果，每一行表示了某个学生某一门课程的成绩。

3）分组，并使用最大值函数max取出最大值

select 学号，
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001',
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001',
max(case 课程号 when '0001'then 成绩 else 0 end)as '课程号0001'
from score
group by 学号;