sql语句 练习

语句 练习"/>

sql语句 练习

一个学生表，一个课程成绩表，怎么找出学生课程的最高分数

--学生哪一科分最高
select 
max(scor), stu.name
from stu inner join score on stu.id = score.stu_id group by  scor

--科目的最高分
select 
max(score.scor), score.course
from stu inner join score on stu.id = score.stu_id group by score.course

查询所有用户的每年的年度报告

-- 查询所有用户的每年的年度报告；
SELECT YEAR( log.log_time ) 年度,SUM( CASE WHEN MONTH ( log.log_time ) = 1 THEN 1 ELSE 0 END ) 一月,SUM( CASE WHEN MONTH ( log.log_time ) = 2 THEN 1 ELSE 0 END ) 二月,SUM( CASE WHEN MONTH ( log.log_time ) = 3 THEN 1 ELSE 0 END ) 三月,SUM( CASE WHEN MONTH ( log.log_time ) = 4 THEN 1 ELSE 0 END ) 四月,SUM( CASE WHEN MONTH ( log.log_time ) = 5 THEN 1 ELSE 0 END ) 五月,SUM( CASE WHEN MONTH ( log.log_time ) = 6 THEN 1 ELSE 0 END ) 六月,SUM( CASE WHEN MONTH ( log.log_time ) = 7 THEN 1 ELSE 0 END ) 七月,SUM( CASE WHEN MONTH ( log.log_time ) = 8 THEN 1 ELSE 0 END ) 八月,SUM( CASE WHEN MONTH ( log.log_time ) = 9 THEN 1 ELSE 0 END ) 九月,SUM( CASE WHEN MONTH ( log.log_time ) = 10 THEN 1 ELSE 0 END ) 十月,SUM( CASE WHEN MONTH ( log.log_time ) = 11 THEN 1 ELSE 0 END ) 十一月,SUM( CASE WHEN MONTH ( log.log_time ) = 12 THEN 1 ELSE 0 END ) 十二月
FROMlog
GROUP BYYEAR ( log.log_time )

-- 查询该学生科目的成绩大于该学生平均分数的科目SELECTa.sid,a.subject_id,a.subject_score,b.avg_score
FROMscore a INNER JOIN (SELECT sid,avg(subject_score) avg_score FROM score GROUP BY sid) b ON a.sid = b.sid
WHEREa.subject_score > b.avg_score

查询科目，该学生科目总分前五

-- 查询科目，该学生科目总分前五
SELECTa.sid,a.subject_id,a.subject_score,b.sum_score
FROMscore a INNER JOIN (SELECT sid,SUM(subject_score) sum_score FROM score GROUP BY sid) b ON a.sid = b.sid
GROUP BYa.sid
ORDER BYsum_score DESC
LIMIT 5