比方说现在这么一张数据表
成绩表grade,里边存储了student_id学生编号,subject_id科目编号,grade成绩。表里每一条数据代表一个学生的一门科目的成绩。其中subject_id 1代表语文,2代表数学,3代表英语。
现有需求,要求生成一张报表,表头是学生ID,语文科目成绩、数学科目成绩,英语科目成绩。而表里的每一行代表一个科目的成绩,需求却需要将三门科目的成绩变为一行,那该怎么办呀?
再应用里做,那还需要应用端单独开发去整理数据。如果该没有应用那又该怎么办呢?
其实可以用sql的聚合函数和if函数嵌套来实现本需求(oracle可以用sum和decode)
-- mysql
SELECT grade.student_id,
SUM(if(grade.subject_id='1',grade.grade,0)) 语文成绩,
SUM(if(grade.subject_id='2',grade.grade,0)) 数学成绩,
SUM(if(grade.subject_id='3',grade.grade,0)) 英语成绩
FROM grade GROUP BY
grade.student_id
-- oracle
SELECT grade.student_id,
SUM(decode(grade.subject_id,'1',grade.grade,0)) 语文成绩,
SUM(decode(grade.subject_id,'2',grade.grade,0)) 数学成绩,
SUM(decode(grade.subject_id,'3',grade.grade,0)) 英语成绩
FROM grade GROUP BY
grade.student_id
用if函数或者decode函数对每一行的subject_id进行判断,如果是对应科目编号,就返回grade,否则返回0。然后用sum根据student_id去进行汇总。这样嵌套使用相当于对应科目的成绩+0+0。这样就满足了需求。
这里要注意一下,行转列的聚合函数一定要用sum不要用count。假设有需求改变,需要统计每门课程的及格次数。要用sum(if(grade>=60,1,0))不要用count,因为count不管该数据列是什么,只要where条件没有过滤,则都算一条。sum可以用if将不符合要求的数据行变为0来过滤。