文章目录
- 11、查询至少有一门课与学号为‘01’的学生所学课程相同的学生的学号和姓名
- 12、查询和‘01’号同学所学课程完全相同的其他同学的学号
- 13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
- 16、检索01课程分数小于60，按分数降序排列的学生信息
- 17、按平均成绩从高到低显示所有同学的所有课程的成绩以及平均成绩
- 18、查询各科成绩最高分，平均分，最低分，及格率，中等率，优良率，优秀率
- 20、查询学生的总成绩并进行排名
11、查询至少有一门课与学号为‘01’的学生所学课程相同的学生的学号和姓名
select s_id,s_name
from student
where s_id in
(
select DISTINCT s_id
from score
where c_id in(
select c_id from score where s_id='01'
) and s_id != '01'
)
扩展
select a.s_id,a.s_name
from student as a
inner join
(
select DISTINCT s_id
from score
where c_id in(
select c_id from score where s_id='01'
) and s_id != '01'
) as b on a.s_id = b.s_id
12、查询和‘01’号同学所学课程完全相同的其他同学的学号
select s_id from score where s_id in(
select s_id from score where c_id in(
select c_id from score where s_id ='01'
) and s_id != '01'
GROUP BY s_id having count(DISTINCT c_id) = (select count(DISTINCT c_id) from score where s_id='01')
)
GROUP BY s_id having count(DISTINCT c_id) = (select count(DISTINCT c_id) from score where s_id='01')
假如一号选了英语和数学两门课，二号选了英语、数学和语文三门课。
还有一种情况、三号选了 英语和物理。
纠正sql语句
- 1、先查询出课程号不一样的学生
- 2、然后再剩下的学号中选择课程数目相同的学生
select s_id,s_name from student where s_id in
(
select s_id from score
where s_id != '01'
GROUP BY s_id
HAVING count(DISTINCT c_id) = (SELECT count(DISTINCT c_id) from score where s_id ='01')
) and s_id not in(
select s_id from score where c_id not in
(
select c_id from score where s_id = '01'
)
)
13、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id,a.s_name,avg(s_score) from student as a
INNER JOIN score as b
on a.s_id = b.s_id
where a.s_id in(
select s_id from score
where s_score < 60
group by s_id
having count(DISTINCT c_id) >= 2
)
group by s_id,s_name
16、检索01课程分数小于60，按分数降序排列的学生信息
- DESC 降序
- ASC 升序
select a.*,b.s_score
from student as a
INNER JOIN score as b
on a.s_id = b.s_id
where b.c_id = '01' and b.s_score < 60
order by b.s_score DESC
17、按平均成绩从高到低显示所有同学的所有课程的成绩以及平均成绩
select a.s_id,a.c_id,a.s_score,b.avg_s_score
from score as a
INNER JOIN
(
select s_id,avg(s_score) as avg_s_score from score
GROUP BY s_id
) as b
on a.s_id = b.s_id
order by b.avg_s_score desc
改进
select
s_id '学号',
MAX(case when c_id = '01' THEN s_score ELSE NULL END) '语文',
MAX(case when c_id = '02' THEN s_score ELSE NULL END) '数学',
MAX(case when c_id = '03' THEN s_score ELSE NULL END) '英文',
avg(s_score) '平均成绩'
from score
group by s_id
ORDER BY avg(s_score) desc
18、查询各科成绩最高分，平均分，最低分，及格率，中等率，优良率，优秀率
select c.c_id ,c.c_name,
max(s.s_score) '最高分',
min(s.s_score) '最低分',
avg(s.s_score) '平均分',
sum(case when s.s_score >= 60 then 1 else 0 END)/count(s_id) '及格率',
sum(case when s.s_score >= 70 and s.s_score < 80 then 1 else 0 END)/count(s_id) '中等率',
sum(case when s.s_score >= 80 and s.s_score <90 then 1 else 0 END)/count(s_id) '优良率',
sum(case when s.s_score >= 90 then 1 else 0 END)/count(s_id) '优秀率'
from score as s
INNER JOIN course as c
on s.c_id = c.c_id
group by c_id
20、查询学生的总成绩并进行排名
select s_id,sum(s_score)
from score
group by s_id
order by sum(s_score) desc
select a.s_id,b.s_name,sum(s_score) '总分'
from score as a
inner JOIN student as b
on a.s_id = b.s_id
group by a.s_id
order by sum(s_score) desc
