常见SQL 报表语句总结

常见的三张表：学生表 课程表  学生成绩表（数据库备份）

题目一：查询出不及格人数最多的单位

select top 1 * from (
select s.SDEPART 单位,COUNT(1) 不及格个数 from StuScoreInfo ss
left join Student s on ss.SID=s.SID
left join Class c on ss.CID=c.CID
where ss.SCORE<60
group by s.SDEPART ) as b order by 不及格个数 desc

题目二：查询出报表格式如下，即各个课程人员年龄分布

/*--------------------------------------
20-30(年龄) 31-40 41-50
语文
数学
政治
太极
---------------------------------------*/

select c.CNAME 科目名称,
SUM(case when s.SAGE between 20 and 30 then 1 else 0 end) '20-30',
SUM(case when s.SAGE between 31 and 40 then 1 else 0 end) '31-40',
SUM(case when s.SAGE between 41 and 50 then 1 else 0 end) '41-50'
from StuScoreInfo ss
left join Student s on ss.SID=s.SID
left join Class c on ss.CID=c.CID
group by c.CNAME