sql进阶 – 行转列
- 2019 年 12 月 26 日
- 筆記

用excel可以轻松实现行转列,用sql怎么实现呢?类似从图一转行成图二的功能:
图一:

图二:

直接上代码。
建表语句:
CREATE TABLE `t_user_score` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键' , `cname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `cource` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , `score` int(11) NULL DEFAULT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci ;
插入测试数据:
INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小许', '语文', '90'); INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小许', '数学', '99'); INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小许', '英语', '88'); INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小张', '语文', '87'); INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小张', '数学', '93'); INSERT INTO `t_user_score` ( `cname`, `cource`, `score`) VALUES ( '小张', '英语', '78');
行转列sql:
SELECT cname , sum(CASE cource WHEN '语文' THEN score ELSE 0 END ) as `语文`, sum(CASE cource WHEN '数学' THEN score ELSE 0 END ) as `数学`, sum(CASE cource WHEN '英语' THEN score ELSE 0 END ) as `英语` FROM t_user_score GROUP BY cname;