Mysql 行转列

效果图

附建表语句&初始化数据语句：

-- 创建学生科目表
CREATE TABLE `studuct_subject` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(32) NOT NULL DEFAULT '',
  `subject` varchar(32) NOT NULL DEFAULT '',
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 初始化学生科目分数数据
insert into studuct_subject(id,user_name,subject,score) 
values (1,"张三","语文",80),(2,"张三","数学",90),(3,"张三","英语",70),(4,"张三","生物",85),
       (5,"李四","语文",80),(6,"李四","数学",90),(7,"李四","英语",70),(8,"李四","生物",85);

简单易懂的固定列写法

下面sql中max函数也可以换为sum等其它聚合函数。

select `user_name`,
    sum(case `subject` when '语文' then IFNULL(`score`, 0) else 0 end) as '语文',
    max(case `subject` when '数学' then IFNULL(`score`, 0) else 0 end) as '数学',
    max(case `subject` when '英语' then IFNULL(`score`, 0) else 0 end) as '英语',
    max(case `subject` when '生物' then IFNULL(`score`, 0) else 0 end) as '生物'
from `studuct_subject`
group by `user_name`;

万能通用的动态生成列写法

set @splitSql = null;
SELECT GROUP_CONCAT(DISTINCT CONCAT('max(if(subject=''',subject,''', score, 0)) as ''',subject, ''''))
    into @splitSql
from studuct_subject;

set @splitSql = CONCAT('select user_name,', @splitSql, ' from studuct_subject group by user_name');
prepare querySql from @splitSql;
execute querySql;
DEALLOCATE prepare querySql;