行转列
假设数据如下
select '小明' as 姓名,'数学' as 科目,'100' as 分数
union
select '小明' as 姓名,'语文' as 科目,'88' as 分数
union
select '小明' as 姓名,'英语' as 科目,'85' as 分数
union
select '小林' as 姓名,'数学' as 科目,'99' as 分数
union
select '小林' as 姓名,'语文' as 科目,'95' as 分数
union
select '小林' as 姓名,'英语' as 科目,'90' as 分数
行转列
select
`姓名`,
SUM(IF(`科目`='数学',分数,0)) AS 数学,
SUM(IF(`科目`='语文',分数,0)) AS 语文,
SUM(IF(`科目`='英语',分数,0)) AS 英语
from
(select '小明' as 姓名,'数学' as 科目,'100' as 分数
union
select '小明' as 姓名,'语文' as 科目,'88' as 分数
union
select '小明' as 姓名,'英语' as 科目,'85' as 分数
union
select '小林' as 姓名,'数学' as 科目,'99' as 分数
union
select '小林' as 姓名,'语文' as 科目,'95' as 分数
union
select '小林' as 姓名,'英语' as 科目,'90' as 分数 ) as tabs
GROUP BY `姓名`
结果如下:
统计汇总
select
ifnull(`姓名`,'成绩汇总') as`姓名` ,
SUM(IF(`科目`='数学',分数,0)) AS 数学,
SUM(IF(`科目`='语文',分数,0)) AS 语文,
SUM(IF(`科目`='英语',分数,0)) AS 英语,
SUM(分数) AS 总分
from
(select '小明' as 姓名,'数学' as 科目,'100' as 分数
union
select '小明' as 姓名,'语文' as 科目,'88' as 分数
union
select '小明' as 姓名,'英语' as 科目,'85' as 分数
union
select '小林' as 姓名,'数学' as 科目,'99' as 分数
union
select '小林' as 姓名,'语文' as 科目,'95' as 分数
union
select '小林' as 姓名,'英语' as 科目,'90' as 分数 ) as tabs
GROUP BY `姓名` WITH ROLLUP;
汇总后结果如下: