mysql横纵表互相转换
1.参考网址:
https://mp.weixin.qq.com/s?__biz=MzAxNjk4ODE4OQ==&mid=2247493491&idx=4&sn=81a8416ce50be5ccbccb2eeef28ed4ee&chksm=9beec601ac994f1785382e114b5bad3db7b019455aca8b2fc768d5d171e6f15250eabb972ede&mpshare=1&scene=23&srcid=1005UiXU1vGzIAXhTP1akx5M&sharer_sharetime=1602208415548&sharer_shareid=9d1e76e919cc0b2f3ca23ed1f5ef67a8#rd
2.准备测试的数据库脚本
# 横表
CREATE TABLE `table_h2z` (
`name` varchar(32) DEFAULT NULL,
`chinese` int(11) DEFAULT NULL,
`math` int(11) DEFAULT NULL,
`english` int(11) DEFAULT NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `table_h2z` */
insert into `table_h2z`(`name`,`chinese`,`math`,`english`) values
('mike',45,43,87),
('lily',53,64,88),
('lucy',57,75,75);
# 纵表
CREATE TABLE `table_z2h` (
`name` varchar(32) DEFAULT NULL,
`subject` varchar(8) NOT NULL DEFAULT '',
`score` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `table_z2h` */
insert into `table_z2h`(`name`,`subject`,`score`) values
('mike','chinese',45),
('lily','chinese',53),
('lucy','chinese',57),
('mike','math',43),
('lily','math',64),
('lucy','math',75),
('mike','english',87),
('lily','english',88),
('lucy','english',75);
3.横表转纵表
SELECT NAME, 'chinese' AS `subject`, chinese AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'math' AS `subject`, math AS `score` FROM table_h2z
UNION ALL
SELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z
4.纵表转横表
SELECT NAME,
SUM(CASE `subject` WHEN 'chinese' THEN score ELSE 0 END) AS chinese,
SUM(CASE `subject` WHEN 'math' THEN score ELSE 0 END) AS math,
SUM(CASE `subject` WHEN 'english' THEN score ELSE 0 END) AS english
FROM table_z2h
GROUP BY NAME