横表和纵表的相互转换

横表和纵表的相互转换

SQL脚本

# 横表
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);

横表转纵表

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

执行结果:
+------+---------+-------+
| 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 |
+------+---------+-------+

纵表转横表

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

执行结果:
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| lily |   53    | 64   |   88 |
| lucy |   57    | 75   |   75 |
| mike |   45    | 43   |   87 |
+------+---------+------+---------+