MySQL查询的横竖转换

目录

• 横转竖
• 竖转横

横转竖

-- 创建表
CREATE TABLE `student` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `yuwen` smallint(3) unsigned NOT NULL DEFAULT '0',
  `yingyu` smallint(3) unsigned NOT NULL DEFAULT '0',
  `math` smallint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO `student`(`id`, `name`, `yuwen`, `yingyu`, `math`) VALUES (0, 'non', 60, 59, 80);
INSERT INTO `student`(`id`, `name`, `yuwen`, `yingyu`, `math`) VALUES (0, 'robin', 80, 80, 40);

源数据

mysql> select * from student;
+----+-------+-------+--------+------+
| id | name  | yuwen | yingyu | math |
+----+-------+-------+--------+------+
|  1 | non   |    60 |     59 |   80 |
|  2 | robin |    80 |     80 |   40 |
+----+-------+-------+--------+------+
2 rows in set (0.02 sec)

横转竖

mysql> select * from (
	select `name` as `name`,`yuwen` as `subject`,`yuwen` as result from student
	UNION ALL
	select `name` as `name`,`yingyu` as `subject`,`yingyu` as result from student
	UNION ALL
	select `name` as `name`,`math` as `subject`,`math` as result from student
) t ORDER BY name;
+-------+---------+--------+
| name  | subject | result |
+-------+---------+--------+
| non   |      59 |     59 |
| non   |      60 |     60 |
| non   |      80 |     80 |
| robin |      40 |     40 |
| robin |      80 |     80 |
| robin |      80 |     80 |
+-------+---------+--------+
6 rows in set (0.03 sec)

---

竖转横

创建表

CREATE TABLE `student1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `result` smallint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据

INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'non', 'yuwen', 60);
INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'non', 'yingyu', 59);
INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'non', 'math', 80);
INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'robin', 'yuwen', 80);
INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'robin', 'yingyu', 80);
INSERT INTO `student1`(`id`, `name`, `subject`, `result`) VALUES (0, 'robin', 'math', 40);

源数据

mysql> select * from student1;
+----+-------+---------+--------+
| id | name  | subject | result |
+----+-------+---------+--------+
|  1 | non   | yuwen   |     60 |
|  2 | non   | yingyu  |     59 |
|  3 | non   | math    |     80 |
|  4 | robin | yuwen   |     80 |
|  5 | robin | yingyu  |     80 |
|  6 | robin | math    |     40 |
+----+-------+---------+--------+
6 rows in set (0.04 sec)

竖转横

mysql> select name as name, max(case subject when 'yuwen' then result else 0 end) as 'yuwen',
    -> max(case subject when 'yingyu' then result else 0 end) as 'yingyu',
    -> max(case subject when 'math' then result else 0 end) as 'math'
    -> from student1 group by name;
+-------+-------+--------+------+
| name  | yuwen | yingyu | math |
+-------+-------+--------+------+
| non   | 60    | 59     | 80   |
| robin | 80    | 80     | 40   |
+-------+-------+--------+------+
2 rows in set (0.07 sec)