mysql - 将行转换为mysql中的列

Question

我的表来自存储过程的输出。看起来像:

col1 
A 
B
C
D

它可以少于或等于 5 行。
我希望它看起来像这样

Column1  Column2 Column3  Column4  column5 
A          B      C         D       NULL

有没有办法在 MYSQL 中实现这一点？

Answer 1

在 MySQL 8.0 之后，该方法变得更加复杂。

第一个查询难点的简单解释
内的子查询

  SELECT 
     GROUP_CONCAT(t.col1 ORDER BY col1 ASC) AS cvs
   , COUNT(*) AS t_count
  FROM 
   t

正在制作一个逗号分隔值列表。然后 SQL 数字生成器结合嵌套 SUBSTRING_INDEX()函数将逗号分隔值拆分为记录。然后由 GROUP_CONCAT() 合并为一个字符串进入用户变量基本上是生成动态 SQL，看起来像 '<value>' AS Column<number>[, ...]这就是SELECT @aggregateSQLPart;给你看

查询

SET @aggregateSQLPart = NULL; 

# set max of GROUP_CONCAT higher as it defaults to 1024 bytes. 
SET SESSION group_concat_max_len = @@max_allowed_packet;

SELECT 
 DISTINCT
   GROUP_CONCAT(CONCAT("'", 
    SUBSTRING_INDEX(
       SUBSTRING_INDEX(
          t.cvs
         , ','
       , number_generator.number
      )
      , ','
      , -1 
    ) , "'" , " AS Column", number_generator.number 
 ))
INTO @aggregateSQLPart                          
FROM (
   SELECT 
     @row := @row + 1 AS number
   FROM (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
      ) row1
      CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) row2
    CROSS JOIN (
      SELECT @row := 0 
    ) init_user_params 
  ) AS number_generator
 CROSS JOIN (
  SELECT 
     GROUP_CONCAT(t.col1 ORDER BY col1 ASC) AS cvs
   , COUNT(*) AS t_count
  FROM 
   t
 ) AS t

WHERE
 number BETWEEN 1 AND t_count;

SELECT @aggregateSQLPart;    


SET @SQL = CONCAT("
 SELECT 
 "
 , @aggregateSQLPart                                                 
);

SELECT @SQL;    

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

结果

| Column1 | Column2 | Column3 | Column4 | Column5 |
| ------- | ------- | ------- | ------- | ------- |
| A       | B       | C       | D       | E       |

参见 demo

注意 不要怀疑它在“测试”服务器上总共平均运行大约 5-10 毫秒的性能。另请注意，我选择了用户变量，这样您就可以看到发生了什么之间。