mysql增长率大于50_SQL必会50题(完结篇）——41-50（代码+结果+思路）

-- 41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩(重点)

select s_id,c_id,s_score from score where s_id =

(

select s_id from

(

select b.s_id, b.s_score from score as b

inner join

(

select s_id from score

group by s_id having count(distinct c_id)>1

) as c on b.s_id = c.s_id

group by b.s_id,b.s_score

) as a

group by s_id having count(s_id)=1)

思路：首先用内连接筛选出学的课程数大于1门的学生id，再用group by函数对s_id,s_score进行分组筛选出只有一条记录的s_id(说明这个同学所学的课程成绩都一样)

-- 42.有类似题，此处省略

-- 43. 统计每门课程的学生选修人数(超过5人的课程才统计)

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

select c_id "课程号", count(distinct s_id) "选修人数" from score

group by c_id having count(distinct s_id)>5

order by 选修人数 desc, 课程号 asc

-- 44. 检索至少选修两门课程的学生学号

select s_id from score

group by s_id having count(distinct c_id)>=2

-- 45. 查询选修了全部课程的学生信息

select * from student where s_id in (

select s_id from score

group by s_id having count(distinct c_id) = (

select count(distinct c_id) from course

))

-- 46.查询各学生的年龄

select s_id, s_birth, floor(datediff(now(),s_birth)/365) from student

Ps：这里用到floor函数向下取整，datediff(A，B)算A-B所差的天数，now()用来调取现在的时间

-- 47.查询没学过‘张三’老师讲授的任意一门课程的学生姓名

select s_name from student where s_id not in (

select s.s_id from score as s

inner join course as c on s.c_id = c.c_id

inner join teacher as t on c.t_id = t.t_id

where t.t_name = '张三')

ps: 思路是先选出学过张三老师课程的学生再not in这些学生选出没学过张三老师课程的学生姓名。另外，如果用group by s_id having …(课程老师是张三)，这种做法错误

-- 48. 查询下周过生日的同学

-- 这道题有待商榷

select * from student where week(concat('2020',substring(s_birth,6,5)),1) = week(now(),1)+1

Ps:这是2020年8月26日的结果

1. week(date, mode) mode=0是以周日作为开始,范围0-53；mode=1是以周一作为一周的开始，范围0-53；mode=2以周日作为开始,范围1-53；mode=3以周一作为开始,范围1-53。

2. substring(string, a, b) 这里a指的是从string的第a位开始，一共取b位(首位记为1)

3. 还要注意这里不同的年份的星期数不同，所以需要将年份统一改成当前年份

4. 但值得商榷的是如果目前是年末时间下周为第二年年初，那么这个方法就失效了

-- 49. 查询本月过生日的人

select * from student where month(s_birth) = month(now())

-- 50.查询下个月过生日的同学

-- 法一

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 mod(month(now())+1,12) = month(s_birth)

Ps:

1.此处为2020.08.26的结果

2. case when 放在where语句中的用法需要注意

3. mod(a,b)取余函数，a表示被取余的数字，b表示除数