在 MySQL 8.0.17 版本中使用 Pivot 从不同表(联合)进行行到列转换 [英] Row to column transformation from different tables (union) in MySQL version 8.0.17 using Pivot
本文介绍了在 MySQL 8.0.17 版本中使用 Pivot 从不同表(联合)进行行到列转换的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要联合存储在 MySql version 8.0.17 上的两个不同表，并使用这个 链接
I need union two different tables stored on MySql version 8.0.17 and on the return set rows values as column name using this link
第一张桌子
+-----------------------+--------+-----+
| contents | sUnity | sID |
+-----------------------+--------+-----+
| Set n.1 | Q400 | 83 |
| - Par 1.1 | Q400 | 84 |
| <b>bold text</b> | Q400 | 85 |
| - Par 1.2 | Q400 | 86 |
| normal text | Q400 | 87 |
| Set n.2 | Q400 | 88 |
| - Par 2.1 | Q400 | 89 |
| <i>italic text</i> | Q400 | 90 |
| - Par 2.2 | Q400 | 91 |
| <u>underline text</u> | Q400 | 92 |
| - Par 2.3 | Q400 | 93 |
+-----------------------+--------+-----+
11 rows in set (0.03 sec)
第二张桌子
+-----------------------+--------+-----+
| contents | sUnity | sID |
+-----------------------+--------+-----+
| Set n.1 | Q410 | 94 |
| - Par 1.1 | Q410 | 95 |
| <b>bold text</b> | Q410 | 96 |
| - Par 1.2 | Q410 | 97 |
| normal text | Q410 | 98 |
| Set n.2 | Q410 | 99 |
| - Par 2.1 | Q410 | 100 |
| <i>italic text</i> | Q410 | 101 |
| - Par 2.2 | Q410 | 102 |
| <u>underline text</u> | Q410 | 103 |
| - Par 2.3 | Q410 | 104 |
+-----------------------+--------+-----+
11 rows in set (0.02 sec)
对于这次退货
+-----------------------+-----------------------+
| 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 |
+-----------------------+-----------------------+
不幸的是，MySQL 没有 PIVOT 函数，这基本上是我想要做的.
Unfortunately MySQL does not have a PIVOT function which is basically for what I trying to do.
我已经尝试过这个查询，我得到了下面的回报
I have tried this query and I have the return below
SET @row_number := 0;
SELECT
MAX(
IF
( sUnity = 'Q400', contents, NULL )) Q400
FROM
(
SELECT
@row_number :=
CASE
WHEN @sUnity = sUnity THEN
@row_number + 1 ELSE 1
END AS num,
@sUnity := sUnity sUnity,
contents
FROM
t_contents_Q400
ORDER BY
sUnity,
sID
) t
GROUP BY
num UNION ALL
SELECT
MAX(
IF
( sUnity = 'Q410', contents, NULL )) Q410
FROM
(
SELECT
@row_number :=
CASE
WHEN @sUnity = sUnity THEN
@row_number + 1 ELSE 1
END AS num,
@sUnity := sUnity sUnity,
contents
FROM
t_contents_Q410
ORDER BY
sUnity,
sID
) t
GROUP BY
num;
+-----------------------+
| Q400 |
+-----------------------+
| Set n.2 |
| Set n.1 |
| - Par 1.1 |
| <b>bold text</b> |
| - Par 1.2 |
| normal text |
| Set n.2 |
| - Par 2.1 |
| <i>italic text</i> |
| - Par 2.2 |
| <u>underline text</u> |
| - Par 2.3 |
+-----------------------+
12 rows in set (0.04 sec)
我的结构和数据表如下
-- ----------------------------
-- 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 ('<b>bold text</b>', 'Q400', 6);
INSERT INTO `t_contents_q400` VALUES ('<i>italic text</i>', 'Q400', 7);
INSERT INTO `t_contents_q400` VALUES ('<u>underline text</u>', 'Q400', 8);
INSERT INTO `t_contents_q400` VALUES ('normal text', 'Q400', 9);
INSERT INTO `t_contents_q400` VALUES ('Set n.1', 'Q400', 10);
INSERT INTO `t_contents_q400` VALUES ('Set n.2', 'Q400', 11);
-- ----------------------------
-- 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);
推荐答案
在 MySQL 8.0+ 中，你可以在每个表中使用 ROW_NUMBER() 窗口函数来获取行号并连接表对此:
In MySQL 8.0+ you can do it with ROW_NUMBER() window function in each table to get a row number and join the tables on that:
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
我假设这 2 个表具有相同的行数，就像您的示例数据一样.
I assume that the 2 tables have the same number of rows, like your sample data.
请参阅演示.
这篇关于在 MySQL 8.0.17 版本中使用 Pivot 从不同表(联合)进行行到列转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
