MYSQL 动态查询结果

今天遇到一个需求需要把这样的

转化为这样的

第一张图是从临时表来的，也就是第二个图的列名是不固定的，如果列名是固定的就是一个简单的行转列，但是现在列名不固定，我们必须使用动态的行转列， 完成这个装换我们需要使用两个知识点

1. 使用动态语句执行sql

SET @sql = CONCAT("CREATE TEMPORARY TABLE dy_table( ser_no varchar(40) null,", @partial_sql, ");");
PREPARE stmt FROM @sql;
EXECUTE stmt;

2. 实现行转列

SELECT order_no, order_type,
MAX(CASE WHEN profile_type ='ASSY_LOC'  AND profile_cat='MPS'  THEN profile_c ELSE NULL  END) AS 'assyLoc',
MAX(CASE WHEN profile_type ='SHIFT'  AND profile_cat='MPS'  THEN profile_c ELSE NULL  END) AS 'shif',
MAX(CASE WHEN profile_type ='PRIORITY'  AND profile_cat='MPS'  THEN profile_c ELSE NULL  END) AS 'priority',
MAX(CASE WHEN profile_type ='REL_DATE'  AND profile_cat='MPS'  THEN profile_d ELSE NULL  END) AS 'releaseDate',
FROM test
WHERE test_type = 28 
AND test_no IN(10504530, 10530423) 
GROUP BY test_no , test_type 

因为需求中有一部分列是固定的，有一部分列是不固定的，我们需要把不固定的列插入一个临时表中，最后和固定的列以及join查出来。大概思路：

1. 将value_s列进行拆分，使用字符串把字段名，和值拆分到一个临时表中

2. 根据字段名去重，构建临时表的创建sql, 和插入sql

3. 执行构建的sql.

但是这里有一个小问题，就是使用的多行转一行的函数GROUP_CONCAT是有长度限制的，默认是1024, 超过这个长度就会被截断，你拼接的sql就是不全面的. 查询长度使用：show variables like "group_concat_max_len";

有三种解决方案, 选择其中一种即可：

 1. SET SESSION group_concat_max_len = 1000000; (session级别的)

  2. SET GLOBAL group_concat_max_len=102400

 3. 在配置文件中

在MySQL配置文件中my.conf或my.ini中添加:

　　#[mysqld]
　　group_concat_max_len=102400

然后 重启MySQL服务

完整代码

DROP TEMPORARY TABLE
 
IF EXISTS assy_detail;

DROP TEMPORARY TABLE
 
IF EXISTS dy_table;

DROP TEMPORARY TABLE
 
IF EXISTS create_dy_table;

DROP TEMPORARY TABLE
 
IF EXISTS dy_column;

CREATE TEMPORARY TABLE assy_detail(
        ser_no  VARCHAR(100) NULL,
        value_s VARCHAR(100) NULL
);

CREATE TEMPORARY TABLE create_dy_table(
        cloumn_name  VARCHAR(100) NULL,
		create_table_sql VARCHAR(100) NULL,
		insert_data_sql VARCHAR(100) NULL
);

CREATE TEMPORARY TABLE dy_column(
    ser_no varchar(40) NULL
    ,value_s VARCHAR(256) NULL
    ,dy_query_sql varchar(100) NULL
    ,cloumn_name varchar(50) NULL
    ,cloumn_value varchar(30) NULL
    );

insert into assy_detail(ser_no,value_s) values ('123', '姓名:张三');
insert into assy_detail(ser_no,value_s) values ('123', '性别:男');
insert into assy_detail(ser_no,value_s) values ('123', '学历:本科');
insert into assy_detail(ser_no,value_s) values ('1233', '学历:本科');


select * from assy_detail;

/*1. create TEMPORARY TABLE dy_table */
insert into create_dy_table(
   cloumn_name
)
SELECT distinct REPLACE(SUBSTRING(value_s, 1, locate(":", value_s) - 1), '-', '_')  
FROM assy_detail;


update create_dy_table
set create_table_sql = CONCAT(cloumn_name, ' varchar(100) null '),
    insert_data_sql = CONCAT(' MAX(CASE WHEN cloumn_name = ', '\'', cloumn_name, '\'', ' THEN ', 'cloumn_value ', ' ELSE NULL END) AS ', '\'',  cloumn_name, '\'');


set @partial_sql = (
    SELECT GROUP_CONCAT(create_table_sql)
    FROM create_dy_table
);
SET @sql = CONCAT("CREATE TEMPORARY TABLE dy_table( ser_no varchar(40) null,", @partial_sql, ");");
PREPARE stmt FROM @sql;
EXECUTE stmt;

/*2 insert data into dy_table*/
INSERT INTO dy_column(
     ser_no
    ,value_s
    ,cloumn_name
    ,cloumn_value
    )
SELECT distinct 
     ser_no
    ,value_s
    ,REPLACE(SUBSTRING(value_s, 1, locate(":", value_s) - 1), '-', '_')
    ,SUBSTRING(value_s, locate(":", value_s) + 1)
FROM assy_detail;

set @partial_sql = (
    SELECT GROUP_CONCAT(insert_data_sql)
    FROM create_dy_table
);
set @sql = concat('insert into dy_table select ser_no, ',@partial_sql, ' from dy_column group by ser_no;');
 
PREPARE stmt FROM @sql;
EXECUTE stmt;

select * from dy_table;