【大数据开发】MySQL数据库——SQL查询语句练习高级进阶②day30

day26里已经发过数据库脚本，直接导入即可

 21、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select s1.sid,s2.sid,sc1.cid,sc1.score,sc2.score 
from student s1,student s2,sc sc1,sc sc2 
where s1.sid!=s2.sid and s1.sid=sc1.sid and s2.sid=sc2.sid and sc1.cid!=sc2.cid and sc1.score=sc2.score;

 22、查询每门功成绩最好的前两名 
 SELECT st.*,sc.c_id,sc.s_score
 FROM student st JOIN score sc 
 ON st.s_id=sc.s_id
 ORDER BY sc.s_score DESC
 LIMIT 0,2

 23、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
SELECT co.c_id,COUNT(co.c_id) counts
FROM student st JOIN score sc 
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY co.c_id
HAVING counts>5
ORDER BY counts DESC, co.c_id

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

SELECT st.*,COUNT(st.s_id)
FROM student st JOIN score sc 
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY st.s_id
HAVING COUNT(st.s_id)>=2

 25、查询选修了全部课程的学生信息 
SELECT st.*
FROM student st JOIN score sc 
ON st.s_id=sc.s_id
JOIN course co
ON sc.c_id=co.c_id
GROUP BY st.s_id
HAVING COUNT(st.s_id) =(
	SELECT COUNT(c_id)
	FROM course 
	)
 

26、查询各学生的年龄，按照出生日期来算。如果当前月日 < 出生年月的月日，则年龄减一
-- 草稿
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
	SELECT DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1 
	WHERE DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d")
)newAge
FROM student st


-- 写法一
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
	CASE 
		WHEN DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d") THEN DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1
		ELSE DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth
	END
)newAge
FROM student st

-- 写法二
SELECT *,DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth age,
(
	IF(
		(DATE_FORMAT(NOW(),"%m-%d")<DATE_FORMAT(st.s_birth,"%m-%d")),
		DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth-1,
		DATE_FORMAT(NOW(),"%Y-%m-%d")-s_birth
	  )
)newAge
FROM student st

 27、查询本周过生日的学生
-- 草稿
SELECT 8-DAYOFWEEK(NOW())
SELECT DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1)

SELECT DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1),"%m-%d")
SELECT DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())),"%m-%d")

-- 答案
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m-%d") 
BETWEEN DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1),"%m-%d")
AND DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())),"%m-%d")

 28、查询下周过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m-%d") 
BETWEEN DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")-(DAYOFWEEK(NOW())-1) + 7,"%m-%d")
AND DATE_FORMAT(DATE_FORMAT(NOW(),"%m%d")+(8-DAYOFWEEK(NOW())) + 7,"%m-%d")

29、查询本月过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m")=DATE_FORMAT(NOW(),"%m") 

30、查询下月过生日的学生
SELECT *
FROM student
WHERE DATE_FORMAT(s_birth,"%m")=DATE_FORMAT(NOW(),"%m")+1