用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;