MySQL 数据透视表 - 行到列.询问

2021-11-20 00:00:00 pivot mysql

我在 mysql 中有这个表:

I have this table in mysql:

<头>
日期姓名标记
2021-02-01亚历克斯7
2021-02-01约翰5
2021-02-01弗兰克4
2021-02-02EVA8
2021-02-02艾丽西亚5

我怎样才能得到与此类似的结果:

How can I get a result similar to this:

<头>
日期亚历克斯约翰弗兰克EVA艾丽西亚
2021-02-01754
2021-02-0285

在Mysql中可以吗?

It is Possible in Mysql?

推荐答案

创建过程

CREATE PROCEDURE pivot (tablename VARCHAR(64),
                        groupname VARCHAR(64),
                        pivotname VARCHAR(64),
                        valuename VARCHAR(64))
BEGIN
SELECT CONCAT('CREATE VIEW to_columnslist AS\n',
              'SELECT DISTINCT CONCAT(\'`\', `', pivotname,'`, \'` VARCHAR(255) path \\\'$."\', ', pivotname,', \'"\\\'\') line\n',
              'FROM ', tablename)
INTO @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT CONCAT(
'SELECT to_json.`', groupname,'`, parsed.*', '\n',
'FROM (SELECT `', groupname,'`, JSON_OBJECTAGG(`', pivotname,'`, `', valuename,'`) json_data', '\n',
'      FROM `', tablename,'`', '\n',
'      GROUP BY `', groupname,'`) to_json', '\n',
'CROSS JOIN JSON_TABLE( json_data,', '\n',
'                       "$" COLUMNS ( ', 
GROUP_CONCAT(line SEPARATOR ',\n                                     '),
' ) ) parsed'
) sql_text
INTO @sql
FROM to_columnslist;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
DROP VIEW to_columnslist;
END

并使用它.

参见 fiddle

相关文章