mysql 行转列

常用的行转列统计 成绩

数据库表结构

select userNames,
 max(IF(subject = '语文',score,0)) as 'chinese',
 max(IF(subject = '数学',score,0)) as 'math',
 max(IF(subject = '英语',score,0)) as 'english',
 sum(score) as'total'
from tb_testuser
group by userNames

或者这样也行：

select userNames,
MAX(CASE subject WHEN '语文' THEN score ELSE 0 END ) 'chinese',
MAX(CASE subject WHEN '数学' THEN score ELSE 0 END )  as 'math',
MAX(CASE subject WHEN '英语' THEN score ELSE 0 END )  as 'english',
sum(score) as'total'
from tb_testuser

group by userNames

查询结果