一条sql 分类汇总

表结构：

stuId   stuName   stuCourse    stuGrade

  001       a                语文             88
  002       b                语文             89
  003       a                数学             98
  004       b                数学             100
  005       a                英语             87
  006       b                英语             86

 

mysql 代码：

  select groupId,stuId,stuName,course,grade from

  (

   select stuId,stuName as groupId,stuName,stuCourse as course,stuGrade as grade from student
   union all
   select null as stuId,CONCAT(totalGrade.stuName,'_')  as groupId, '总成绩'  as stuName, null as   

   course,totalGrade.grade as grade from
   (select stuName ,sum(stuGrade)  as grade from student group by stuName)  totalGrade

  ) temp order by groupId

 

结果如下所示：

 groupId stuId   stuName   stuCourse    stuGrade

    a         005       a                英语               87
    a         003       a                数学               98
    a         001       a                语文               88
    a_       null     总成绩            null               273
    b         006       b                英语               86
    b         004       b                数学               100
    b         002       b                语文               89
    b_       null     总成绩           null                275