时间戳

首页 > 解决方案 > MySQL Pivot 将行旋转到列

mysql - MySQL Pivot 将行旋转到列

问题描述

这是我的 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

PIVOT() 函数开启t_chapter

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');

标签： mysqlpivot

解决方案

您可以 CROSS JOINt_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

请参阅演示。

推荐阅读