MYSQL行转列-动态修改

CREATE TABLE `tb_score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` varchar(20) NOT NULL COMMENT '用户id',
`subject` varchar(20) DEFAULT NULL COMMENT '科目',
`score` double DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (1, '001', '语文', 90);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (2, '001', '数学', 92);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (3, '001', '英语', 80);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (4, '002', '语文', 88);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (5, '002', '数学', 90);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (6, '002', '英语', 75.5);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (7, '003', '语文', 70);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (8, '003', '数学', 85);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (9, '003', '英语', 90);
INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (10, '003', '政治', 85);

 

 

1、使用case...when....then 进行行转列

SELECT userid,
SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治'
FROM tb_score
group by userid

 

 

 使用IF() 进行行转列：

SELECT userid,
SUM(IF(`subject`='语文',score,0)) as '语文',
SUM(IF(`subject`='数学',score,0)) as '数学',
SUM(IF(`subject`='英语',score,0)) as '英语',
SUM(IF(`subject`='政治',score,0)) as '政治'
FROM tb_score
GROUP BY userid

 

利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为Total

SELECT IFNULL(userid,'total') AS userid,
SUM(IF(`subject`='语文',score,0)) AS 语文,
SUM(IF(`subject`='数学',score,0)) AS 数学,
SUM(IF(`subject`='英语',score,0)) AS 英语,
SUM(IF(`subject`='政治',score,0)) AS 政治,
SUM(IF(`subject`='total',score,0)) AS total
FROM(
SELECT userid,IFNULL(`subject`,'total') AS `subject`,SUM(score) AS score
FROM tb_score
GROUP BY userid,`subject`
WITH ROLLUP
HAVING userid IS NOT NULL
)AS A
GROUP BY userid

WITH ROLLUP;

 

又是一个面试题 未写出来的答案