select a.ClassName,a.CourseName,sum(不及格) as 不及格,sum(差) as 差,sum(中等) as 中等,sum(好) as 好 ,sum(不及格)+sum(差)+sum(中等)+sum(好) as 班级总人数 from (select StudentID,ClassName,CourseName,1 as 不及格,0 as 差,0 as 中等,0 as 好 from StudentScore where ScoreRemark='fail' union all
select StudentID,ClassName,CourseName,0 as 不及格,1 as 差,0 as 中等,0 as 好 from StudentScore where ScoreRemark='low' union all
select StudentID,ClassName,CourseName,0 as 不及格,0 as 差,1 as 中等,0 as 好 from StudentScore where ScoreRemark='medium' union all
select StudentID,ClassName,CourseName,0 as 不及格,0 as 差,0 as 中等,1 as 好 from StudentScore where ScoreRemark='good' )a group by ClassName,CourseName
在这里需要注意的是要给某些字段加别名以示区别!
在激烈的市场竞争环境下,商人对市场环境的把握和决策直接关系到市场的趋势和机遇,对客户需求的快速相应能够占尽市场的先机。在当今社会的日常商务活动中,以往积累的相关商业数据能够为后续的业务活动提供良好的参考和向导。早在10余年前,商业智能(Business Intelligence,下称BI)厂商就已经 ......