mysql8 动态行转列

MySQL 8 中可以使用 `JSON_TABLE` 函数来实现将行转列的操作。

假设有以下表结构：

CREATE TABLE scores (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  subject VARCHAR(50),
  score INT
);

其中包含学生的姓名、学科和成绩，我们想要把学科作为列名，学生姓名作为行名，成绩作为值，可以使用以下 SQL 语句：

SELECT 
  name, 
  IFNULL(`math`, 0) AS math, 
  IFNULL(`english`, 0) AS english, 
  IFNULL(`chinese`, 0) AS chinese
FROM
  (SELECT 
     name,
     subject, 
     score 
   FROM 
     scores) AS t
  JOIN 
    JSON_TABLE(
      (SELECT 
         CONCAT('{"', GROUP_CONCAT(CONCAT_WS('":', subject, score)), '}') AS json_data
       FROM 
         scores 
       GROUP BY 
         name),
      '$.*'
      COLUMNS(
        `math` INT PATH '$."math"',
        `english` INT PATH '$."english"',
        `chinese` INT PATH '$."chinese"'
      )
    ) AS jt 
    ON t.subject = jt.subject AND t.score = jt.score
GROUP BY 
  name;

首先，内层的子查询使用 `GROUP_CONCAT` 函数将每个学生的成绩转换为 JSON 格式的字符串。然后，使用 `JSON_TABLE` 函数解析这些 JSON 数据，并将它们转换为列。最后，使用 `IFNULL` 函数将空值替换为 0，并按学生姓名进行分组。

这样就可以将行转列，并将学科作为列名，学生姓名作为行名，成绩作为值。