mysql 行转列

把上图这种一行数据，转换成许多行，转换成一列

 

先创建一个表sequencetest，表中包含数字，一行中有多少列就包含多少数字

CREATE TABLE `sequencetest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8

 

 

SELECT 
ss.id,
tt.mn_num,
tt.MONITOR_TIME,
tt.PARA_TYPE,
REPLACE(
SUBSTRING(
SUBSTRING_INDEX(course, ',', ss.id),
CHAR_LENGTH(
SUBSTRING_INDEX(course, ',', ss.id - 1)
) + 1
),
',',
''
) AS monitor_parameter,
REPLACE(
SUBSTRING(
SUBSTRING_INDEX(course_overnum, ',', ss.id),
CHAR_LENGTH(
SUBSTRING_INDEX(course_overnum, ',', ss.id - 1)
) + 1
),
',',
''
) AS over_num    
FROM
sequenceTEST ss 
CROSS JOIN 
(SELECT 
mn_num,
LEFT(MONITOR_TIME, 4) AS MONITOR_TIME,
PARA_TYPE,
CONCAT(
IFNULL(d1, 'null'),
',',
IFNULL(d2, 'null'),
',',
IFNULL(d3, 'null'),
',',
IFNULL(d4, 'null'),
',',
IFNULL(d5, 'null'),
',',
IFNULL(d6, 'null'),
',',
IFNULL(d7, 'null'),
',',
IFNULL(d8, 'null'),
',',
IFNULL(d9, 'null'),
',',
IFNULL(d10, 'null'),
',',
IFNULL(d11, 'null'),
',',
IFNULL(d12, 'null'),
',',
IFNULL(d13, 'null'),
',',
IFNULL(d14, 'null'),
',',
IFNULL(d15, 'null'),
',',
IFNULL(d16, 'null'),
',',
IFNULL(d17, 'null'),
',',
IFNULL(d18, 'null'),
',',
IFNULL(d19, 'null'),
',',
IFNULL(d20, 'null'),
',',
IFNULL(d21, 'null'),
',',
IFNULL(d22, 'null'),
',',
IFNULL(d23, 'null'),
',',
IFNULL(d24, 'null'),
',',
IFNULL(d25, 'null'),
',',
IFNULL(d26, 'null'),
',',
IFNULL(d27, 'null'),
',',
IFNULL(d28, 'null'),
',',
IFNULL(d29, 'null'),
',',
IFNULL(d30, 'null'),
',',
IFNULL(d31, 'null')
) course,
CONCAT(
IFNULL(d1_overnum, 'null'),
',',
IFNULL(d2_overnum, 'null'),
',',
IFNULL(d3_overnum, 'null'),
',',
IFNULL(d4_overnum, 'null'),
',',
IFNULL(d5_overnum, 'null'),
',',
IFNULL(d6_overnum, 'null'),
',',
IFNULL(d7_overnum, 'null'),
',',
IFNULL(d8_overnum, 'null'),
',',
IFNULL(d9_overnum, 'null'),
',',
IFNULL(d10_overnum, 'null'),
',',
IFNULL(d11, 'null'),
',',
IFNULL(d12_overnum, 'null'),
',',
IFNULL(d13_overnum, 'null'),
',',
IFNULL(d14_overnum, 'null'),
',',
IFNULL(d15_overnum, 'null'),
',',
IFNULL(d16_overnum, 'null'),
',',
IFNULL(d17_overnum, 'null'),
',',
IFNULL(d18_overnum, 'null'),
',',
IFNULL(d19_overnum, 'null'),
',',
IFNULL(d20_overnum, 'null'),
',',
IFNULL(d21_overnum, 'null'),
',',
IFNULL(d22_overnum, 'null'),
',',
IFNULL(d23_overnum, 'null'),
',',
IFNULL(d24_overnum, 'null'),
',',
IFNULL(d25_overnum, 'null'),
',',
IFNULL(d26_overnum, 'null'),
',',
IFNULL(d27_overnum, 'null'),
',',
IFNULL(d28_overnum, 'null'),
',',
IFNULL(d29_overnum, 'null'),
',',
IFNULL(d30_overnum, 'null'),
',',
IFNULL(d31_overnum, 'null')
) course_overnum,
31 AS num 
FROM
table_month_data 
WHERE LEFT(MONITOR_TIME, 7) = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 MONTH), '%Y-%c')) tt 
WHERE ss.id <= tt.num

 

先取出要转换的字段，用“，”拼接（防止null值丢失，如果为NULL，则拼接字符串‘null’），之后再和ss表结合拆分成多行。

本例中转换了两列，一个dxx列，一个dxx_overnum列。