语句 练习"/>
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
更多推荐
sql语句 练习
发布评论