MySQL数据透视将行旋转到列

Question

这是我的MySQL数据库版本

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set

我已经创建了一个MySQL数据库和两个相关的表，其中一个表的行将被转换为像PIVOT((函数这样的列

表t_name

mysql> SELECT sName FROM `t_name`;
+-------+
| sName |
+-------+
| 1D    |
| 1E    |
| 1L    |
| 2A    |
| 2C    |
| 2F    |
| 2H    |
| 2P    |
| 3B    |
| 3E    |
| 3H    |
| 4B    |
| 4D    |
| 4G    |
| 5H    |
+-------+
15 rows in set

表t_chapter

mysql> SELECT * FROM `t_chapter`;
+----------+--------+-----+
| sCHAPTER | sTITLE | sID |
+----------+--------+-----+
|        1 | ES     |   1 |
|        2 | SA     |   2 |
|        3 | ECO    |   3 |
|        4 | PER    |   4 |
|        5 | ESEM   |   5 |
|        6 | CMR    |   6 |
|        7 | SVRE   |   7 |
|        8 | AVA    |   8 |
|        9 | INT    |   9 |
|       10 | SPM    |  10 |
+----------+--------+-----+
10 rows in set

t_chapter上的PIVOT((函数

mysql> SELECT 
CASE WHEN sCHAPTER = "1" THEN NULL END "ES",
CASE WHEN sCHAPTER = "2" THEN NULL END "SA",
CASE WHEN sCHAPTER = "3" THEN NULL END "ECO",
CASE WHEN sCHAPTER = "4" THEN NULL END "PER",
CASE WHEN sCHAPTER = "5" THEN NULL END "ESEM",
CASE WHEN sCHAPTER = "6" THEN NULL END "CMR",
CASE WHEN sCHAPTER = "7" THEN NULL END "SVRE",
CASE WHEN sCHAPTER = "8" THEN NULL END "AVA",
CASE WHEN sCHAPTER = "9" THEN NULL END "INT",
CASE WHEN sCHAPTER = "10" THEN NULL END "SMP"
FROM `t_chapter`;
+------+------+------+------+------+------+------+------+------+------+
| ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
+------+------+------+------+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+------+------+------+------+
10 rows in set

现在我需要t_chapter上的并集，t_name的值用于此返回

+-------+------+------+------+------+------+------+------+------+------+------+
| sName | ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
+-------+------+------+------+------+------+------+------+------+------+------+
| 1D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1L    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2A    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2C    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2F    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2P    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3E    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 3H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4B    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4D    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 4G    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5H    | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+------+------+------+------+------+------+------+------+------+------+

如何解决此问题？

有什么建议吗？

下方的表格

-- ----------------------------
-- Table structure for t_chapter
-- ----------------------------
DROP TABLE IF EXISTS `t_chapter`;
CREATE TABLE `t_chapter` (
`sCHAPTER` int(11) DEFAULT NULL,
`sTITLE` char(150) DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`),
UNIQUE KEY `sCHAPTER` (`sCHAPTER`)
) ENGINE=MyISAM;
-- ----------------------------
-- Records of t_chapter
-- ----------------------------
INSERT INTO `t_chapter` VALUES ('1', 'ES', '1');
INSERT INTO `t_chapter` VALUES ('2', 'SA', '2');
INSERT INTO `t_chapter` VALUES ('3', 'ECO', '3');
INSERT INTO `t_chapter` VALUES ('4', 'PER', '4');
INSERT INTO `t_chapter` VALUES ('5', 'ESEM', '5');
INSERT INTO `t_chapter` VALUES ('6', 'CMR', '6');
INSERT INTO `t_chapter` VALUES ('7', 'SVRE', '7');
INSERT INTO `t_chapter` VALUES ('8', 'AVA', '8');
INSERT INTO `t_chapter` VALUES ('9', 'INT', '9');
INSERT INTO `t_chapter` VALUES ('10', 'SPM', '10');
-- ----------------------------
-- Table structure for t_name
-- ----------------------------
DROP TABLE IF EXISTS `t_name`;
CREATE TABLE `t_name` (
`sName` char(10) DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`),
UNIQUE KEY `sName` (`sName`)
) ENGINE=MyISAM;
-- ----------------------------
-- Records of t_name
-- ----------------------------
INSERT INTO `t_name` VALUES ('4G', '1');
INSERT INTO `t_name` VALUES ('4B', '2');
INSERT INTO `t_name` VALUES ('3H', '3');
INSERT INTO `t_name` VALUES ('2F', '4');
INSERT INTO `t_name` VALUES ('2C', '5');
INSERT INTO `t_name` VALUES ('2A', '6');
INSERT INTO `t_name` VALUES ('1L', '7');
INSERT INTO `t_name` VALUES ('1E', '8');
INSERT INTO `t_name` VALUES ('1D', '9');
INSERT INTO `t_name` VALUES ('5H', '10');
INSERT INTO `t_name` VALUES ('4D', '11');
INSERT INTO `t_name` VALUES ('3E', '12');
INSERT INTO `t_name` VALUES ('3B', '13');
INSERT INTO `t_name` VALUES ('2H', '14');
INSERT INTO `t_name` VALUES ('2P', '15');

Answer 1

您可以交叉加入t_name到查询的不同值：

WITH cte AS (
SELECT DISTINCT
CASE WHEN sCHAPTER = '1' THEN NULL END ES,
CASE WHEN sCHAPTER = '2' THEN NULL END SA,
CASE WHEN sCHAPTER = '3' THEN NULL END ECO,
CASE WHEN sCHAPTER = '4' THEN NULL END PER,
CASE WHEN sCHAPTER = '5' THEN NULL END ESEM,
CASE WHEN sCHAPTER = '6' THEN NULL END CMR,
CASE WHEN sCHAPTER = '7' THEN NULL END SVRE,
CASE WHEN sCHAPTER = '8' THEN NULL END AVA,
CASE WHEN sCHAPTER = '9' THEN NULL END `INT`,
CASE WHEN sCHAPTER = '10' THEN NULL END SMP
FROM t_chapter
)
SELECT t.sName, c.*
FROM t_name t CROSS JOIN cte c

请参阅演示
结果：

| sName | ES   | SA   | ECO  | PER  | ESEM | CMR  | SVRE | AVA  | INT  | SMP  |
| ----- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| 1D    | null | null | null | null | null | null | null | null | null | null |
| 1E    | null | null | null | null | null | null | null | null | null | null |
| 1L    | null | null | null | null | null | null | null | null | null | null |
| 2A    | null | null | null | null | null | null | null | null | null | null |
| 2C    | null | null | null | null | null | null | null | null | null | null |
| 2F    | null | null | null | null | null | null | null | null | null | null |
| 2H    | null | null | null | null | null | null | null | null | null | null |
| 2P    | null | null | null | null | null | null | null | null | null | null |
| 3B    | null | null | null | null | null | null | null | null | null | null |
| 3E    | null | null | null | null | null | null | null | null | null | null |
| 3H    | null | null | null | null | null | null | null | null | null | null |
| 4B    | null | null | null | null | null | null | null | null | null | null |
| 4D    | null | null | null | null | null | null | null | null | null | null |
| 4G    | null | null | null | null | null | null | null | null | null | null |
| 5H    | null | null | null | null | null | null | null | null | null | null |

附带说明，您可以在不使用表t_chapter:的情况下获得相同的结果

WITH cte AS (
SELECT 
NULL ES,
NULL SA,
NULL ECO,
NULL PER,
NULL ESEM,
NULL CMR,
NULL SVRE,
NULL AVA,
NULL `INT`,
NULL SMP
)
SELECT t.sName, c.*
FROM t_name t CROSS JOIN cte c

请参阅演示

或者：

WITH cte(ES, SA, ECO, PER, ESEM, CMR, SVRE, AVA, `INT`, SMP) AS (
SELECT NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
)
SELECT t.sName, c.*
FROM t_name t CROSS JOIN cte c

请参阅演示