MySQL 8.0.17 版中使用 Pivot 的行到列转换 [英] Row to column transformation in MySQL version 8.0.17 using Pivot
问题描述
不幸的是,MySQL
没有 PIVOT 函数
,这基本上是我想要做的.
我需要使用下面的存储过程将返回的行值设置为列名
+-----+-------------+-------------+--------+|身份证 |sUnity_Q400 |sUnity_Q410 |sUnity |+-----+------------+------------+--------+|6 |Q400 |空 |Q400 ||73 |空 |Q410 |Q410 ||9 |Q400 |空 |Q400 ||75 |空 |Q410 |Q410 ||14 |Q400 |空 |Q400 ||78 |空 |Q410 |Q410 ||16 |Q400 |空 |Q400 ||80 |空 |Q410 |Q410 ||71 |Q400 |空 |Q400 ||82 |空 |Q410 |Q410 ||7 |Q400 |空 |Q400 ||74 |空 |Q410 |Q410 ||15 |Q400 |空 |Q400 ||79 |空 |Q410 |Q410 ||17 |Q400 |空 |Q400 ||81 |空 |Q410 |Q410 ||10 |Q400 |空 |Q400 ||76 |空 |Q410 |Q410 ||4 |Q400 |空 |Q400 ||72 |空 |Q410 |Q410 ||12 |Q400 |空 |Q400 ||77 |空 |Q410 |Q410 |+-----+------------+------------+--------+22 行(0.05 秒)
下表的结构和数据
-- ------------------------------ t_contents_s3sv_1_2021 的表结构-- --------------------------如果存在`t_contents_s3sv_1_2021`,则删除表;创建表`t_contents_s3sv_1_2021`(`contents` varchar(255) 默认为空,`sUnity` varchar(50) 默认为空,`sID` int(11) NOT NULL AUTO_INCREMENT,主键 (`sID`) 使用 BTREE,使用 BTREE 的唯一索引 `contents`(`contents`, `sUnity`)) 引擎 = InnoDB;-- ---------------------------- t_contents_s3sv_1_2021 的记录-- --------------------------INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q400', 6);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q410', 73);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q400', 9);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q410', 75);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q400', 14);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q410', 78);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q400', 16);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q410', 80);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q400', 71);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q410', 82);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q400', 7);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q410', 74);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>斜体文本</i>', 'Q400', 15);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>斜体文本</i>', 'Q410', 79);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>下划线文本</u>', 'Q400', 17);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>下划线文本</u>', 'Q410', 81);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('normal text', 'Q400', 10);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('普通文本', 'Q410', 76);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q400', 4);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q410', 72);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q400', 12);INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q410', 77);
如果您将 [sID] 替换为一个行号,该行号将按 ID 的顺序链接 [sUnity] 值,则您应该能够进行透视
select max(IF(sUnity = 'Q400', t.contents, NULL)) [Q400], max(IF(sUnity = 'Q410', t.contents, NULL)) [Q410]from ( select row_number() over (partition by sUnity order by sID) as rn, tcs.sUnity, tcs.contents来自 dbo.t_contents_s3sv_1_2021 as tcs) as t按 rn 分组按 rn 订购
如果 row_number 不可用,您可以使用它来模拟 row_number
SET @row_number := 0;SELECT MAX(IF(sUnity = 'Q400', 内容, NULL)) Q400,MAX(IF(sUnity = 'Q410', 内容, NULL)) Q410从(选择@row_number:=CASE当@sUnity = sUnity然后@row_number + 1别的1结束为编号,@sUnity:=sUnity sUnity,内容从t_contents_s3sv_1_2021订购者sUnity, sID) t按数量分组;
Unfortunately MySQL
does not have a PIVOT function
which is basically for what I trying to do.
I need on the return set rows values as column name using the stored procedure below this link
I populate a table of a database MySql version 8.0.17
, with an external file in csv format.
This is the table filled with data from the external csv file
+-----------------------+--------+-----+
| contents | sUnity | sID |
+-----------------------+--------+-----+
| Set n.1 | Q400 | 4 |
| - Par 1.1 | Q400 | 6 |
| <b>bold text</b> | Q400 | 7 |
| - Par 1.2 | Q400 | 9 |
| normal text | Q400 | 10 |
| Set n.2 | Q400 | 12 |
| - Par 2.1 | Q400 | 14 |
| <i>italic text</i> | Q400 | 15 |
| - Par 2.2 | Q400 | 16 |
| <u>underline text</u> | Q400 | 17 |
| - Par 2.3 | Q400 | 71 |
| Set n.1 | Q410 | 72 |
| - Par 1.1 | Q410 | 73 |
| <b>bold text</b> | Q410 | 74 |
| - Par 1.2 | Q410 | 75 |
| normal text | Q410 | 76 |
| Set n.2 | Q410 | 77 |
| - Par 2.1 | Q410 | 78 |
| <i>italic text</i> | Q410 | 79 |
| - Par 2.2 | Q410 | 80 |
| <u>underline text</u> | Q410 | 81 |
| - Par 2.3 | Q410 | 82 |
+-----------------------+--------+-----+
22 rows in set (0.03 sec)
Now I need this return I mean set rows values as column name
+-----------------------+-----------------------+
| 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 |
| 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 |
+-----------------------+-----------------------+
Stored procedure and return ( I'm sorry but if I adding the SP code this question is not released... )
+-----+-------------+-------------+--------+
| sID | sUnity_Q400 | sUnity_Q410 | sUnity |
+-----+-------------+-------------+--------+
| 6 | Q400 | NULL | Q400 |
| 73 | NULL | Q410 | Q410 |
| 9 | Q400 | NULL | Q400 |
| 75 | NULL | Q410 | Q410 |
| 14 | Q400 | NULL | Q400 |
| 78 | NULL | Q410 | Q410 |
| 16 | Q400 | NULL | Q400 |
| 80 | NULL | Q410 | Q410 |
| 71 | Q400 | NULL | Q400 |
| 82 | NULL | Q410 | Q410 |
| 7 | Q400 | NULL | Q400 |
| 74 | NULL | Q410 | Q410 |
| 15 | Q400 | NULL | Q400 |
| 79 | NULL | Q410 | Q410 |
| 17 | Q400 | NULL | Q400 |
| 81 | NULL | Q410 | Q410 |
| 10 | Q400 | NULL | Q400 |
| 76 | NULL | Q410 | Q410 |
| 4 | Q400 | NULL | Q400 |
| 72 | NULL | Q410 | Q410 |
| 12 | Q400 | NULL | Q400 |
| 77 | NULL | Q410 | Q410 |
+-----+-------------+-------------+--------+
22 rows in set (0.05 sec)
Structure and data of table below
-- ----------------------------
-- Table structure for t_contents_s3sv_1_2021
-- ----------------------------
DROP TABLE IF EXISTS `t_contents_s3sv_1_2021`;
CREATE TABLE `t_contents_s3sv_1_2021` (
`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_s3sv_1_2021
-- ----------------------------
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q400', 6);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.1', 'Q410', 73);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q400', 9);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 1.2', 'Q410', 75);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q400', 14);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.1', 'Q410', 78);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q400', 16);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.2', 'Q410', 80);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q400', 71);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('- Par 2.3', 'Q410', 82);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q400', 7);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<b>bold text</b>', 'Q410', 74);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>italic text</i>', 'Q400', 15);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<i>italic text</i>', 'Q410', 79);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>underline text</u>', 'Q400', 17);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('<u>underline text</u>', 'Q410', 81);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('normal text', 'Q400', 10);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('normal text', 'Q410', 76);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q400', 4);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.1', 'Q410', 72);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q400', 12);
INSERT INTO `t_contents_s3sv_1_2021` VALUES ('Set n.2', 'Q410', 77);
you should be able to pivot if you replace [sID] with a rownumber that will link the [sUnity] values by order of their ID's
select max(IF(sUnity = 'Q400', t.contents, NULL)) [Q400]
, max(IF(sUnity = 'Q410', t.contents, NULL)) [Q410]
from ( select row_number() over (partition by sUnity order by sID) as rn
, tcs.sUnity
, tcs.contents
from dbo.t_contents_s3sv_1_2021 as tcs) as t
group by rn
order by rn
if row_number is not available you can use this to mimic row_number
SET @row_number := 0;
SELECT MAX(IF(sUnity = 'Q400', contents, NULL)) Q400,
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_s3sv_1_2021
ORDER BY
sUnity, sID
) t
GROUP BY num;
这篇关于MySQL 8.0.17 版中使用 Pivot 的行到列转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!