mysql学习训练记录及笔记（三）

接着训练二

I、sql训练

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select * from score t1 join score t2 on t1.s_id=t2.s_id where t1.s_score =t2.s_score and t1.c_id <> t2.c_id

42、查询每门功成绩最好的前两名

select t1.s_id,t1.c_id,t1.s_score,t1.ran
from (
select s_id,c_id,s_score,rank() over(partition by c_id order by s_score) ran from score 
) t1
where t1.ran <3

43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c_id,count(s_id) count_n from score group by c_id having count_n>5 order by count_n desc

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

select s_id from score group by s_id having count(c_id)>1

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

select t1.* from student t1 join score t2 on  t1.s_id =t2.s_id group by s_id having count(c_id) >2

46、查询各学生的年龄

-- 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select s_name,s_birth,
(date_format(now(),'%Y')-date_format(s_birth,'%Y')-(case when date_format(s_birth,'%m%d')<date_format(now(),'%m%d') then 1 else 0 end)) as 年龄
from student

47、查询本周过生日的学生

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

48、查询下周过生日的学生

select * from student where week(s_birth)=week(now())+1

49、查询本月过生日的学生

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

50、查询下月过生日的学生

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

II、个人总结（本套过于简单）

1、日期相关函数