mysql横纵表互相转换

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