浪了一阵，我又回来刷题了💪lol
P.S. 50题中存在重复
简单类题目（答案一致）
- 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id, count(*) num
from Score
group by c_id
having num > 5
order by num desc, c_id;
- 检索至少选修两门课程的学生学号
select s_id
from Score
group by s_id
having count(*) >= 2;
- 查询选修了全部课程的学生信息
select stu.*
from Student stu
join Score s on stu.s_id = s.s_id
group by s.s_id
having count(*) = (select count(distinct c_id) from Course);
- 查询课程成绩在70分以上的姓名、课程名称和分数
select s_name,c_name,s_score
from Score s
join Course c on s.c_id = c.c_id
join Student st on st.s_id = s.s_id
where s_score > 70
;
- 查询不及格的课程并按课程号从大到小排列
select sc.s_id, s_name, c.c_id, c_name, s_score
from Score sc
join Course c on sc.c_id = c.c_id
join Student st on st.s_id = sc.s_id
where s_score < 60
order by sc.c_id desc;
- 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
select s.s_id,s_name
from Score s
join Student st
on s.s_id = st.s_id
where c_id = '03'
and s_score > 80;
- 查询姓“猴”的老师的个数
select count(*)
from Teacher
where t_name like '猴%'
- 查询课程编号为“02”的总成绩
select sum(s_score)
from Score
where c_id = '02';
- 查询没有学全所有课的学生的学号、姓名
select stu.s_id, stu.s_name
from Score sco
right join Student stu
on sco.s_id = stu.s_id
group by s_id, s_name
having count(c_id) != (select count(distinct c_id) from Course) -- 找出所有课是有几门
-- 大佬上面用的是<,我用的是!=
-- 大佬用的是inner join没考虑一门都没选的人
- 检索"01"课程分数小于60，按分数降序排列的学生信息
select stu.*,s_score
from Score sco
join Student stu
on sco.s_id = stu.s_id
where c_id = '01'
and s_score < 60
order by s_score desc;
- 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select stu.s_id,stu.s_name, avg(s_score) mean
from Student stu
join Score sco
on stu.s_id = sco.s_id
group by stu.s_id,stu.s_name
having mean>=85;
- 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select c_id, avg(s_score) mean
from Score
group by c_id
order by mean, c_id desc;
- 查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s_name,s_score
from Course c
join Score s on s.c_id = c.c_id
join Student stu on stu.s_id = s.s_id
where c.c_name = '数学'
and s_score < 60;
- 查询出只有两门课程的全部学生的学号和姓名
select distinct stu.s_id,s_name
from Student stu
join Score sco
on stu.s_id = sco.s_id
group by stu.s_id
having count(distinct c_id) = 2;
- 查询男生、女生人数
select s_sex,count(*)
from Student
group by s_sex
- 查询名字中含有"风"字的学生信息
select *
from Student
where s_name like '%风%';
- 查询1990年出生的学生名单
select *
from Student
where year(s_birth) = '1990';
- 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
select sc.c_id,co.c_name,
sum(case when s_score<=100 and s_score>=85 then 1 else 0 end) "[100,85]",
sum(case when s_score<85 and s_score>=70 then 1 else 0 end) "[70,85)",
sum(case when s_score<70 and s_score>=60 then 1 else 0 end) "[60,70)",
sum(case when s_score<60 and s_score>=0 then 1 else 0 end) "[0,60)"
from Score sc
join Course co
on sc.c_id = co.c_id
group by c_id
大佬的写法前面添加了描述性统计量，注意 count(case when) 里面 else NULL
- 查询学生平均成绩及其名次
select s_id, avg(s_score), row_number() over (order by avg(s_score) desc)
from Score
group by s_id;
- 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
注：及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
-- 自己和大佬写的一致 🐂
select cou.c_name 课程名字, new.*
from Course cou
join
(select c_id 课程编号,max(s_score) 最高分, min(s_score) 最低分, avg(s_score) 平均分,
avg(case when s_score>=60 then 1 else 0 end) 及格率,
avg(case when s_score>=70 and s_score<80 then 1 else 0 end) 中等率,
avg(case when s_score>=80 and s_score<90 then 1 else 0 end) 优良率,
avg(case when s_score>=90 then 1 else 0 end) 优秀率
from Score
group by c_id) new
on cou.c_id = new.课程编号
中等类题目 （答案正确，但可优化）
- 查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的信息及课程分数
- 自己
select stu.*, s3.s1, s3.s2
from Student stu
join
-- 01比02高的成绩所对应的学生id以及两科成绩
(select t1.*, t2.s2
from
-- 找出每个学生01的课程成绩
(select s_id, s_score s1
from Score S1
where c_id = 1) t1
join
-- 找出每个学生02的课程成绩
(select s_id, s_score s2
from Score S
where c_id = 2) t2
on t1.s_id = t2.s_id
where s1 > s2) s3
on stu.s_id = s3.s_id
;
- 大佬
select stu.*, t1.s1, t2.s2
from Student stu
inner join
-- 找出每个学生01的课程成绩
(select s_id, s_score s1
from Score S1
where c_id = '01') t1
on t1.s_id = stu.s_id
inner join
-- 找出每个学生02的课程成绩
(select s_id, s_score s2
from Score S
where c_id = '02') t2
on t1.s_id = t2.s_id
where s1 > s2
;
- 反思总结
（1） join默认是inner join
（2）大佬是三表直接join，我创建了新表，再两表join
（3）注意细节，课程代码是VARCHAR，要用引号
- 查询平均成绩大于60分的学生的学号和平均成绩
- 自己和大佬的答案一致
select s_id,avg(s_score) 平均成绩
from Score
group by s_id
having 平均成绩 > 60;
- 类似题目
查询所有课程成绩小于60分的学生的学号、姓名
（歧义：是找出每一个课程成绩<60？还是说找出每门课成绩都小于60？）
最开始的思路想用 ALL
select s_id
from Score
where 60 > all(s_score) ;
但发现错误60并不是一列的名字，
SQL ALL运算符的语法：WHERE column_name comparison_operator ALL (subquery)
于是接着想到了逻辑等价，即所有课程成绩小于60 == 最高分小于60
select s1.s_id, s1.s_name
from Student s1
-- 先找出每个学生的最低分
join
(select s_id, min(s_score)
from Score
group by s_id
having min(s_score) < 60) s2
on s1.s_id = s2.s_id
;
但是参考大佬的答案，发现自己没有考虑无效成绩NULL，也就是没参加考试
方法一：迂回解题
select *
from Student
where s_id not in
(select s_id from Score where s_score >= 60);
-- 大于等于60的反面是NULL+小于60
方法二：正面解题
select stu.s_id, stu.s_name, avg(ifnull(sco.s_score,0)) as avg
from Student stu
left join Score sco
on stu.s_id = sco.s_id
group by stu.s_id
having avg < 60;
-- 或者
select stu.s_id, stu.s_name, avg(sco.s_score) as avg
from Student stu
left join Score sco
on stu.s_id = sco.s_id
group by stu.s_id
having (avg < 60) or isnull(avg)
;
- 反思总结
（1）没考虑到NULL
（2）不用使用min，想复杂了。只要有一门课大于等于60，s_id就会在子查询中出现，再使用not in反选，省时省力。如果正面想in+小于60的条件，一是没考虑到NULL，二是没考虑到所有科目，只要有一门小于60就会选出该id。
- 查询所有学生的学号、姓名、选课数、总成绩
- 自己
select stu.s_id, stu.s_name, sco.选课数, ifnull(sco.总成绩,0) 总成绩
from Student stu
left join
(select s_id, count(*) 选课数, sum(ifnull(s_score,0)) 总成绩
from Score
group by s_id) sco
on stu.s_id = sco.s_id
- 大佬
（这里原博的作者提供的答案没有考虑没参加考试的同学，此处写的是小破站陆小亮老师的做法）
select a.s_id, a.s_name, count(b.c_id),
sum(case when b.s_score is null then 0 else b.s_score end)
from student a
left join score b
on a.s_id = b.s_id
group by s_id,s_name
- 反思总结
（1）ifnull和case when可以互相替代
（2）select后面的出现的字段最好在group by后面出现过
- 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
- 自己
⚠️ 别老嵌套了 用联接！
select st.s_name, s_score
from Score s
join Student st on s.s_id = st.s_id
where c_id = (
select c_id
from Course
where t_id = (select t_id from Teacher where t_name = '张三'))
order by s_score desc
limit 1;
- 大佬
“SQL SERVER 中用top，MYSQL 用 limit”
limit 起始位置，取的条数
select top 1 st.s_id, st.s_name, sc.s_score
from Course co
join Score sc on sc.c_id = co.c_id
join Teacher te on te.t_id = co.t_id
where te.t_name = "张三"
order by sc.s_score desc;
- 查询没学过“张三”老师课的学生的学号、姓名
查询学过“张三”老师所教的所有课的同学的学号、姓名（略）
- 自己
-- （4）没上过的学号对应的名字
select s_id, s_name
from Student
where s_id not in(
-- （3）哪些学生上过这门课
select s_id
from Score
where c_id in #老师没准上的不止一门课
-- （2）找张三上过的课程代码
(select c_id
from Course
where t_id =
-- （1）先找到老师的工号
(select t_id
from Teacher
where t_name = '张三')))
- 大佬
select s_id, s_name
from Student
where s_id not in(
select sc.s_id from Score sc
inner join Course co on sc.c_id = co.c_id
inner join Teacher te on co.t_id = te.t_id
where te.t_name = '张三');
- 反思总结
嵌套思路清晰容易懂，但是性能差，最好用联接或者拆开嵌套存成临时表
- 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
- 自己
select stu.s_id, s_name
from Student stu
where s_id in(
select s1.s_id
from Score s1, Score s2
where s1.s_id = s2.s_id
and s1.c_id = '01'
and s2.c_id = '02'
);
- 大佬
select s_id, s_name from Student
where s_id in
(
select a.s_id from
(select s_id from Score where c_id = '01') a
inner join
(select s_id from Score where c_id = '02') b
on a.s_id = b.s_id);
- 类似题目
查询学过编号为“01”的课程但没学过编号为“02”的课程的学生的信息
-- 我和大佬的版本一致
select *
from Student
where s_id in(
select s_id
from Score
where s_id in
(select s_id from Score where c_id = '01')
and s_id not in
(select s_id from Score where c_id = '02'));
- 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
- 自己
select new.s_id, stu.s_name
from
(select distinct s_id
from Score
where c_id in
(select c_id
from Score
where s_id = '01')) new
join Student stu
on stu.s_id = new.s_id;
- 大佬
select distinct st.*
from Student st
inner join Score sc
on st.s_id = sc.s_id
where sc.c_id in
(select c_id from Score where s_id = '01')
and st.s_id != '01';
- 反思总结
没有考虑不包括01本身。
- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
- 自己
select sco.s_id, stu.s_name, avg(s_score) 平均成绩
from Score sco
join Student stu
on sco.s_id = stu.s_id
where sco.s_id in (
-- 先计算出每个人不及格的门数，然后筛选满足条件的id
select s_id
from Score
group by s_id
having sum(if(s_score<60,1,0)) >=2)
group by sco.s_id
;
- 大佬
select stu.s_id, stu.s_name, avg(s_score) 平均成绩
from Score sco
join Student stu
on sco.s_id = stu.s_id
where sco.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
;
- 查询各科成绩前三名的记录（不考虑成绩并列情况）
- 自己
select *
from
(select *,
row_number() over (partition by c_id order by s_score desc) 排序
from Score) new
where 排序 in (1,2,3)
- 大佬
select c_id,
max(case when m=1 then s_score else null end) 'NO.1',
max(case when m=2 then s_score else null end) 'NO.2',
max(case when m=3 then s_score else null end) 'NO.3'
from
(select st.s_id, st.s_name, st.s_birth, st.s_sex,c_id,s_score,
row_number() over(partition by c_id order by s_score desc) m
from Score sc
inner join Student st
on sc.s_id = st.s_id) a
where m in (1,2,3)
group by c_id;
难搞类题目
- 查询和“01”号同学所学课程完全相同的其他同学的学号
我没想出来，看了原博对应第12题的答案，逻辑有问题，选了三门课也不一定和01完全相同。
- 各路神仙的做法(自己稍微修改版)
select s_id
from Score
where s_id != '01'
group by s_id
having GROUP_CONCAT(c_id order by c_id) =
(select GROUP_CONCAT(c_id order by c_id)
from Score
where s_id = '01'
group by s_id);
GROUP_CONCAT:将多条数据合并为一条
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])
-- 假设01同学选了A、B两节课
-- 先选出其他同学所学的课程不在A、B的同学，排除；
-- 再在剩下的同学中去判断所选课程数是否为2门
select * 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 distinct s_id from Score
where c_id not in(select c_id from Score where s_id = '01'));
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 自己
select s1.*,s2.mean
from Score s1
left join
(select s_id, avg(s_score) mean
from Score
group by s_id) s2
on s1.s_id = s2.s_id
order by mean desc;
但希望得到的格式应该是
| 学号 | 学科1 | 学科2 | 平均分 |
|---|---|---|---|
| xx | xx | … … | xxx |
所以，sql如何行列互换？
- 大佬
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;
-- max在这里无意义，换成min/sum等统计函数都行，因为一个学生一个学科只会有一条记录，group by要与select中要保持一致，要用统计函数去取值。
- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
- 自己
最开始理解成所有课程合在一起，进行排序
select stu.*, new.s_score
from Student stu
join
(select *
from Score
order by s_score desc
limit 1,2) new
on stu.s_id = new.s_id;
然后分科
select stu.*, new.c_id, new.s_score, new.result
from
(select s_id, c_id, s_score,
row_number() over(partition by c_id order by s_score desc) result
from Score) new
join Student stu
on new.s_id = stu.s_id
where result = 2 or result = 3;
-- 和大佬的结果一致
- 大佬
select *
from (select st.s_id, st.s_name, st.s_birth, st.s_sex,c_id, s_score,
row_number() over(partition by c_id order by s_score desc) m
from score sc
inner join student st
on sc.s_id = st.s_id) a
where m in (2,3);
- 反思总结
where m in (2,3)
存疑类题目
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
（题目存在歧义）
原博的意思是找出所有的课成绩都一样的同学，也就是这个同学所有科都考了一样的分数。还是说只要两门课成绩一样就行。
select *
from Score s1, Score s2
where s1.c_id < s2.c_id
and s1.s_score = s2.s_score;
- 查询所有学生的课程及分数情况
🚩大神的讲解🚩
但我纠结的点，有没有其他的方法，如果课程賊多的话，也这么搞么？
select sd.s_id, sd.s_name,
max(case when c.c_name = '语文' then s_score else null end) as '语文',
max(case when c.c_name = '数学' then s_score else null end) as '数学',
max(case when c.c_name = '英语' then s_score else null end) as '英语'
from score as s
join course as c on s.c_id = c.c_id
right join student as sd on s.s_id = sd.s_id
group by sd.s_id, sd.s_name;
- 查询每门课程选修的学生数
感觉原博的答案不对，万一哪门课没人选…
select c.c_id, c_name, count(*)
from Score s
right join Course c
on s.c_id = c.c_id
group by c.c_id,c_name
- 按各科成绩进行排序，并显示排名
“ROW_NUMBER()函数将针对SELECT语句返回的每一行,从1开始编号,赋予其连续的编号。”
–语法形式：ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL2)
–解释： 根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
- 自己
select *,
row_number() over (partition by c_id order by s_score desc) 排序
from Score
排序函数的区别
（1）row_number() 没有重复值的排序（即使记录相等也不重复）
（2）dense_rank() 连续排序
（3）rank() 跳跃排序
- 大佬
感觉原博写的有点问题，没有分学科
- 查询学生的总成绩并进行排名
原博是直接order by排序，但我觉得不等于排名吧…
select s_id, sum(s_score) 总成绩, row_number() over (order by sum(s_score) desc) 排名
from Score
group by s_id
- 查询不同老师所教不同课程平均分从高到低显示（有歧义 按老师还是按课程？）
select t_id, co.c_id, avg(s_score) mean
from Score sc
join Course co
on sc.c_id = co.c_id
group by c_id, t_id
order by mean desc;
日期类题目（容易出问题）
有关时间的函数
| 函数名 | 含义 | 函数名 | 含义 |
|---|---|---|---|
| curdate() | 返回当前时间 | curtime() | 返回当前时间 |
| now() | 返回当前日期和时间 | unix_timestamp(date) | 返回日期date的UNIX时间戳 |
| from_unixtime | 返回unix时间戳的日期值 | week(date) | 返回日期date为一年中的第几周 |
| year(date) | 返回日期date的年份 | hour/minute(time) | 返回time的小时值/分钟值 |
| monthname(date) | 返回date的月份名 | date_format(date,fmt) | 返回按字符串fmt格式化日期date值 |
| datediff(exp,exp2) | 返回起始时间和结束时间之间的天数 | date_add(date, interval expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
- 查询各学生的年龄（精确到月份）
- 自己
select datediff(curdate(),s_birth)/365 from Student ;
- 大佬
-- 用的是SQL Server
-- DATEDIFF(datapart,startdate,enddate)
select s_id,s_birth,
datediff(month,s_birth,curdate())/12 from Student ;
- 查询下月过生日的同学
select *
from Student
where month(s_birth) = month(now())+1;
-- 然而，跨年咋整？
select month('2019-12-30')+1; -- 返回的值为13
-- 更新升级
select *
from Student
where month(s_birth) = mod(month(now())+1,12);
求余函数 MOD(x,y) 返回 x 被 y 除后的余数
- 大佬
select * from Student where
case when month(now()) = 12 then month(s_birth) = 1 else month(s_birth) = month(now())+1 end;
- 查询下周过生日的学生
惯性思维
select *
from Student
where week(s_birth) = week(now())+1;
week函数的运用：WEEK(date[,mode])
原博的有问题，原因🐂，完全没想到！
那么如何解决？我想的解决方案👇
select *
from Student
where week(concat(year(now()),substring(s_birth,5))) = week(now())+1;
但接着阿婆主提出了又一个问题🤦♂️
我想的解决办法是👇
select week('2019-12-30'); #52
select week('2019-01-01'); #0
-- 所以还要考虑跨年因素
select *
from Student
where (
case when week(now()) = 52 then week(concat(year(now()),substring(s_birth,5))) =0
else week(concat(year(now()),substring(s_birth,5))) = week(now())+1 end);
有问题欢迎👏讨论啊:>
- 总结
（1）尽量不要用嵌套，用join函数
（2）时间类题目因为循环，经常会出问题，要多考虑‘边界’
（3）脑子是个好东西，加油💪
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
