5/29 SQL练习题摘要

9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

select * from Student 
WHERE S_id in(
select  S_id from SC
where C_id in (select C_id from SC where S_id='01')
and S_id not in ('01')
GROUP BY S_id HAVING COUNT(C_id)=3)

 

15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

select a.c_id, a.s_id, a.score, count(b.score)+1 as rank
from sc as a
left join sc as b
on a.score<b.score and a.c_id = b.c_id
group by a.c_id, a.s_id,a.score
order by a.c_id, rank ASC;

 

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select a.* ,count(b.score)+1 rank from sc a left join sc b
on a.c_id = b.c_id and (a.score < b.score or (a.score = b.score and a.s_id > b.s_id))
group by a.c_id,a.s_id
order by a.c_id,rank

 

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

#使用变量，因为select优先级>order by优先级，所以需要自连接

set @crank = 0;

select a.s_id,b.a 总分,@crank := @crank +1 as rank from (

select s_id,sum(score) as a from SC

group by s_id

order by a desc)b

 

18. 查询各科成绩前三名的记录
-- 先用C_id 左连接 a表同科目分数和b表比较，有大于的就显示信息，没有就显示空值
-- 用a.s_id,a.c_id,a.score 对b表进行分组
-- 对分组后的b表s_id进行计数筛选，空值就是0，说明分数排第一
-- 对a表c_id,score进行排序
select a.S_id,a.C_id,a.score,b.* from SC a 
left join SC b on a.C_id=b.C_id and a.score<b.score
group by a.S_id,a.C_id,a.score
having COUNT(b.S_id)<3
order by a.C_id,a.score desc

 

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

select * from SC where C_id = '01' and score in(select score from SC where C_id = '01' group by score having count(Score) > 1)
union all
select * from SC where C_id = '02' and score in(select score from SC where C_id = '02' group by score having count(Score) > 1)
union all
select * from SC where C_id = '03' and score in(select score from SC where C_id = '03' group by score having count(Score) > 1)

 

36. 查询每门功成绩最好的前两名

#联合查询中子句使用group by ，子句需要用（）

(select C_id,score from SC 
where C_id = '01'
ORDER BY score desc LIMIT 2)
union all
(select C_id,score from SC 
where C_id = '02'
ORDER BY score desc LIMIT 2)
union all
(select C_id,score from SC 
where C_id = '03'
ORDER BY score desc LIMIT 2)

 

40. 查询各学生的年龄，只按年份来算 

#curdate() 获取当前日期（年月日）

#DATE_FORMAT() 数据转换

SELECT Sname,DATE_FORMAT(curdate(),'%Y')-DATE_FORMAT(Sage,'%Y')年龄
from student;

 

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

#TIMESTAMPDIFF() 计算时间差，返回月份

#truncate() 截取数据，小数点后0位

select sname,truncate(TIMESTAMPDIFF(month,Sage,curdate())/12,0)年龄 from student
ORDER BY 年龄

 

42. 查询本周过生日的学生

#方法一

# WEEKOFYEAR() 计算数据是今年的第几周

SELECT
 *
FROM
 Student
WHERE
 WEEKOFYEAR(
  DATE_FORMAT(NOW(), '%Y%m%d')
 ) = WEEKOFYEAR(
  DATE_FORMAT(Sage, '%Y%m%d') - DATE_FORMAT(Sage, '%Y') * 10000 + DATE_FORMAT(NOW(), '%Y') * 10000
 )
 

#方法二
 select * from student where week(Sage)=week(curdate())