柒索

一个头脑聪明,五肢发达的男人。

0%

MySQL数据库中[列转行]一列转多行的问题

问题概述

这个问题是使用ETL进行数据导入而产生的,需要使用Informatica对数据进行采集从ODS库导入到项目库中,且需要读取项目库中的配置,Informatica不涉及代码开发,只能读取表或者视图来配置参数,而项目库中的配置表则是用户在页面进行录入的,此时需要对页面录入的数据进行拼接给工具使用IN来使用,众所周知数据库中IN所传入的参数长度是有限制的,所以使用SELECT GROUP_CONCAT(CODE) FROM COUNTRY;的这种方式并不可取,会产生性能问题,为了解决这个现象,于是有了如下实现代码

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for country
-- ----------------------------
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of country
-- ----------------------------
BEGIN;
INSERT INTO `country` VALUES ('ABW', 'Aruba', 'North America', 'Caribbean', 193.00, NULL, 103000, 78.4, 828.00, 793.00, 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 129, 'AW');
INSERT INTO `country` VALUES ('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', 652090.00, 1919, 22720000, 45.9, 5976.00, NULL, 'Afganistan/Afqanestan', 'Islamic Emirate', 'Mohammad Omar', 1, 'AF');
INSERT INTO `country` VALUES ('AGO', 'Angola', 'Africa', 'Central Africa', 1246700.00, 1975, 12878000, 38.3, 6648.00, 7984.00, 'Angola', 'Republic', 'José Eduardo dos Santos', 56, 'AO');
INSERT INTO `country` VALUES ('AIA', 'Anguilla', 'North America', 'Caribbean', 96.00, NULL, 8000, 76.1, 63.20, NULL, 'Anguilla', 'Dependent Territory of the UK', 'Elisabeth II', 62, 'AI');
INSERT INTO `country` VALUES ('ALB', 'Albania', 'Europe', 'Southern Europe', 28748.00, 1912, 3401200, 71.6, 3205.00, 2500.00, 'Shqipëria', 'Republic', 'Rexhep Mejdani', 34, 'AL');
INSERT INTO `country` VALUES ('AND', 'Andorra', 'Europe', 'Southern Europe', 468.00, 1278, 78000, 83.5, 1630.00, NULL, 'Andorra', 'Parliamentary Coprincipality', '', 55, 'AD');
INSERT INTO `country` VALUES ('ANT', 'Netherlands Antilles', 'North America', 'Caribbean', 800.00, NULL, 217000, 74.7, 1941.00, NULL, 'Nederlandse Antillen', 'Nonmetropolitan Territory of The Netherlands', 'Beatrix', 33, 'AN');
INSERT INTO `country` VALUES ('ARE', 'United Arab Emirates', 'Asia', 'Middle East', 83600.00, 1971, 2441000, 74.1, 37966.00, 36846.00, 'Al-Imarat al-´Arabiya al-Muttahida', 'Emirate Federation', 'Zayid bin Sultan al-Nahayan', 65, 'AE');
INSERT INTO `country` VALUES ('ARG', 'Argentina', 'South America', 'South America', 2780400.00, 1816, 37032000, 75.1, 340238.00, 323310.00, 'Argentina', 'Federal Republic', 'Fernando de la Rúa', 69, 'AR');
INSERT INTO `country` VALUES ('ARM', 'Armenia', 'Asia', 'Middle East', 29800.00, 1991, 3520000, 66.4, 1813.00, 1627.00, 'Hajastan', 'Republic', 'Robert Kotšarjan', 126, 'AM');
INSERT INTO `country` VALUES ('ASM', 'American Samoa', 'Oceania', 'Polynesia', 199.00, NULL, 68000, 75.1, 334.00, NULL, 'Amerika Samoa', 'US Territory', 'George W. Bush', 54, 'AS');
INSERT INTO `country` VALUES ('ATA', 'Antarctica', 'Antarctica', 'Antarctica', 13120000.00, NULL, 0, NULL, 0.00, NULL, '–', 'Co-administrated', '', NULL, 'AQ');
INSERT INTO `country` VALUES ('ATF', 'French Southern territories', 'Antarctica', 'Antarctica', 7780.00, NULL, 0, NULL, 0.00, NULL, 'Terres australes françaises', 'Nonmetropolitan Territory of France', 'Jacques Chirac', NULL, 'TF');
INSERT INTO `country` VALUES ('ATG', 'Antigua and Barbuda', 'North America', 'Caribbean', 442.00, 1981, 68000, 70.5, 612.00, 584.00, 'Antigua and Barbuda', 'Constitutional Monarchy', 'Elisabeth II', 63, 'AG');

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

实现代码

20为每行配置的参数,可根据实际情况进行调整

1
2
3
4
5
6
7
SELECT T.ROW_ID, GROUP_CONCAT(COUNTRYCODE) COUNTRYCODE  FROM (
SELECT ROW_ID,COUNTRYCODE FROM (
SELECT FLOOR(@ROWNUM/20) +1 AS 'ROW_ID', @ROWNUM:=@ROWNUM + 1 AS 'ROW', A.COUNTRYCODE
FROM CITY A ,(SELECT @ROWNUM:=0) B
) A
GROUP BY A.ROW_ID,A.ROW,A.COUNTRYCODE
) T GROUP BY T.ROW_ID;

结果展示

1
2
3
4
5
6
7
8
9
10
11
12
AUS,AUT,ABW,AZE,AFG,BDI,AGO,BEL,AIA,BEN,ALB,AND,ANT,ARE,ARG,ARM,ASM,ATA,ATF,ATG
BRA,BRB,BRN,BTN,BVT,BWA,BFA,CAF,BGD,CAN,BGR,CCK,BHR,CHE,BHS,BIH,BLR,BLZ,BMU,BOL
COK,COL,COM,CPV,CRI,CUB,CXR,CYM,CYP,CZE,CHL,DEU,CHN,DJI,CIV,DMA,CMR,DNK,COD,COG
ECU,GHA,EGY,GIB,ERI,ESH,ESP,EST,ETH,FIN,FJI,FLK,FRA,FRO,FSM,GAB,DOM,GBR,DZA,GEO
HND,HRV,GIN,HTI,GLP,HUN,GMB,IDN,GNB,IND,GNQ,GRC,GRD,GRL,GTM,GUF,GUM,GUY,HKG,HMD
KAZ,KEN,KGZ,KHM,KIR,KNA,IOT,KOR,IRL,KWT,IRN,LAO,IRQ,LBN,ISL,ISR,ITA,JAM,JOR,JPN
LTU,LUX,LVA,MAC,MAR,MCO,MDA,MDG,MDV,MEX,LBR,MHL,LBY,MKD,LCA,MLI,LIE,MLT,LKA,LSO
MNP,NLD,MOZ,NOR,MRT,MSR,MTQ,MUS,MWI,MYS,MYT,NAM,NCL,NER,NFK,NGA,MMR,NIC,MNG,NIU
PRT,PRY,NPL,PSE,NRU,PYF,NZL,QAT,OMN,REU,PAK,PAN,PCN,PER,PHL,PLW,PNG,POL,PRI,PRK
SLB,SLE,SLV,SMR,SOM,SPM,ROM,STP,RUS,SUR,RWA,SVK,SAU,SVN,SDN,SEN,SGP,SGS,SHN,SJM
TGO,THA,TJK,TKL,TKM,TMP,TON,TTO,TUN,TUR,SWE,TUV,SWZ,TWN,SYC,TZA,SYR,UGA,TCA,TCD
URY,ZWE,USA,UZB,VAT,VCT,VEN,VGB,VIR,VNM,VUT,WLF,WSM,YEM,YUG,UKR,ZAF,UMI,ZMB