零散的MySQL基础总是记不住？看这一篇如何拯救你( 三 ) _MySQL基础

# 横表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` */insertinto `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` */insertinto `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);横表转纵表SELECT NAME, 'chinese' AS `subject`,chinese AS `score` FROM table_h2zUNION ALLSELECT NAME, 'math' AS `subject`,math AS `score` FROM table_h2zUNION ALLSELECT NAME, 'english' AS `subject`, english AS `score` FROM table_h2z执行结果
+------+---------+-------+| name | subject | score |+------+---------+-------+| 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 |+------+---------+-------+9 rows in set (0.00 sec)纵表转横表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 englishFROM table_z2hGROUP BY NAME执行结果
+------+---------+------+---------+| name | chinese | math | english |+------+---------+------+---------+| lily |53 |64 |88 || lucy |57 |75 |75 || mike |45 |43 |87 |+------+---------+------+---------+3 rows in set (0.00 sec)