在 MySQL 8.0.17 版本中使用 Pivot 从不同的表和不同的行号(联合)进行行到列转换 [英] Row to column transformation from different tables and different rows number (union) in MySQL version 8.0.17 using Pivot

问题描述

在 MySQL 8.0+ 中使用 ROW_NUMBER() 窗口函数 在每个表中获取一个行号并将其上的表连接起来以进行来自不同表的行到列转换(数据透视表)

In MySQL 8.0+ using ROW_NUMBER() window function in each table to get a row number and join the tables on that for row to column transformation from different tables (pivot-table)

如果 2 个表具有相同的行数，则该函数可以正常工作，例如您的示例数据.

The function working correctly if the 2 tables have the same number of rows, like your sample data.

WITH 
  cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sID) rn FROM t_contents_q400),
  cte2 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY sID) rn FROM t_contents_q410)
SELECT 
c1.contents Q400, 
c2.contents Q410
FROM cte1 c1 
INNER JOIN cte2 c2 ON c2.rn = c1.rn;

+-----------------------+-----------------------+
| Q400                  | Q410                  |
+-----------------------+-----------------------+
| Set n.1               | Set n.1               |
| - Par 1.1             | - Par 1.1             |
| <b>bold text</b>      | <b>bold text</b>      |
| - Par 1.2             | - Par 1.2             |
| normal text           | normal text           |
| Set n.2               | Set n.2               |
| - Par 2.1             | - Par 2.1             |
| <i>italic text</i>    | <i>italic text</i>    |
| - Par 2.2             | - Par 2.2             |
| <u>underline text</u> | <u>underline text</u> |
| - Par 2.3             | - Par 2.3             |
+-----------------------+-----------------------+
11 rows in set (0.03 sec)

但是如果 2 个表的行数不同，则某些行不会合并.

But if the 2 tables have the different number of rows some rows are not merged.

就我而言，我在第一个表 t_contents_q400 中总共有 14 行，在第二个表 t_contents_q410 中我总共有 11 行.

On my case I have in the first table t_contents_q400 14 rows total and in the second table t_contents_q410 I have 11 rows total.

返回时第一个表 t_contents_q400 的这些行不是提取的.

On the return these rows of the first table t_contents_q400 are not extract.

Set n.3         Q400
- Par 3.1       Q400
<i>text</i>     Q400

我的结构和数据表如下

-- ----------------------------
-- Table structure for t_contents_q400
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_q400`;
CREATE TABLE `t_contents_q400`  (
  `contents` varchar(255) DEFAULT NULL,
  `sUnity` varchar(50) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `contents`(`contents`, `sUnity`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents_q400
-- ----------------------------
INSERT INTO `t_contents_q400` VALUES ('- Par 1.1', 'Q400', 1);
INSERT INTO `t_contents_q400` VALUES ('- Par 1.2', 'Q400', 2);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.1', 'Q400', 3);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.2', 'Q400', 4);
INSERT INTO `t_contents_q400` VALUES ('- Par 2.3', 'Q400', 5);
INSERT INTO `t_contents_q400` VALUES ('- Par 3.1', 'Q400', 6);
INSERT INTO `t_contents_q400` VALUES ('<b>bold text</b>', 'Q400', 7);
INSERT INTO `t_contents_q400` VALUES ('<i>italic text</i>', 'Q400', 8);
INSERT INTO `t_contents_q400` VALUES ('<i>text</i>', 'Q400', 9);
INSERT INTO `t_contents_q400` VALUES ('<u>underline text</u>', 'Q400', 10);
INSERT INTO `t_contents_q400` VALUES ('normal text', 'Q400', 11);
INSERT INTO `t_contents_q400` VALUES ('Set n.1', 'Q400', 12);
INSERT INTO `t_contents_q400` VALUES ('Set n.2', 'Q400', 13);
INSERT INTO `t_contents_q400` VALUES ('Set n.3', 'Q400', 14);

-- ----------------------------
-- Table structure for t_contents_q410
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_q410`;
CREATE TABLE `t_contents_q410`  (
  `contents` varchar(255) DEFAULT NULL,
  `sUnity` varchar(50) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`) USING BTREE,
  UNIQUE INDEX `contents`(`contents`, `sUnity`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_contents_q410
-- ----------------------------
INSERT INTO `t_contents_q410` VALUES ('- Par 1.1', 'Q410', 1);
INSERT INTO `t_contents_q410` VALUES ('- Par 1.2', 'Q410', 2);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.1', 'Q410', 3);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.2', 'Q410', 4);
INSERT INTO `t_contents_q410` VALUES ('- Par 2.3', 'Q410', 5);
INSERT INTO `t_contents_q410` VALUES ('<b>bold text</b>', 'Q410', 6);
INSERT INTO `t_contents_q410` VALUES ('<i>italic text</i>', 'Q410', 7);
INSERT INTO `t_contents_q410` VALUES ('<u>underline text</u>', 'Q410', 8);
INSERT INTO `t_contents_q410` VALUES ('normal text', 'Q410', 9);
INSERT INTO `t_contents_q410` VALUES ('Set n.1', 'Q410', 10);
INSERT INTO `t_contents_q410` VALUES ('Set n.2', 'Q410', 11);

推荐答案

您本质上想要一个 full join，而 MySQL 不支持.处理此问题的一种方法是 union all 和聚合:

You essentially want a full join, which MySQL does not support. One method to handle this is union all and aggregation:

SELECT MAX(CASE WHEN which = 'Q400' THEN contents END) as Q400,
       MAX(CASE WHEN which = 'Q401' THEN contents END) as Q401       
c2.contents Q410
FROM ((SELECT 'Q400' as which, c.*,
              ROW_NUMBER() OVER (ORDER BY sID) as seqnum
       FROM t_contents_q400 c
      ) UNION ALL
      (SELECT 'Q401' as which, c.*,
              ROW_NUMBER() OVER (ORDER BY sID) as seqnum
       FROM t_contents_q401 c
      )
     ) q
GROUP BY seqnum;

这篇关于在 MySQL 8.0.17 版本中使用 Pivot 从不同的表和不同的行号(联合)进行行到列转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！