查询各个学生的每一科的成绩与总分以及平均分（保留2位小数），并且按照成绩的平均分 进行倒序排序

需求

1. 查询各个学生的每一科的成绩与总分以及平均分（保留2位小数），并且按照成绩的平均分
   进行倒序排序，要求展示方式：

---

$data=array( '0'=>array( 'id'=>'', 's_id'=>'', 'name'=>'', 'sub1_score'=>0.0, 'sub2_score'=>0.0, 'sub3_score'=>0.0, 'total_score'=>0.0, 'avg_score'=>0.0 ), …… '9'=>array( 'id'=>'', 's_id'=>'', 'name'=>'', 'sub1_score'=>0.0, 'sub2_score'=>0.0, 'sub3_score'=>0.0, 'total_score'=>0.0, 'avg_score'=>0. ) );

原生SQL

select s.id,s.s_id,s.name, SUM(case c.name when 'IT0' then convert(g.score,decimal(5,2)) else 0 end) as 'IT0', SUM(case c.name when 'IT1' then convert(g.score,decimal(5,2)) else 0 end) as 'IT1', SUM(case c.name when 'IT2' then convert(g.score,decimal(5,2)) else 0 end) as 'IT2', convert(sum(g.score)/3,decimal(5,2)) as '平均成绩', convert(sum(score),decimal(5,2)) as total from student s,subject c,score g where s.s_id=g.s_id and c.id=g.sub_id group by s.s_id order by sum(g.score) desc