mysql - MySQL透视表查询带有动态列的截断键值

我试图动态地旋转下表中的键值对。

+-----------------+-----------------------------+-------+
| id | category   | name                        | value |
+-----------------+-----------------------------+-------+
|  1 | acme       | 2fa                         |   0   |
|  2 | acme       | abc_processing_date         | today |
|  3 | acme       | activate_new_approve_person |   1   |
|  4 | acme       | activate_new_schdule        |   1   |
|  5 | acme       | additional_footer_for_person|  NULL |
+-----------------+-----------------------------+-------+

在下面运行我的查询，我得到了错误
'-new-schedule,IFNULL(IF(z_tmp_admin_system_settings.name = 'additional_footer_fo' at line 1
这是用Taryn在MySQL pivot table query with dynamic columns中描述的方法开发的。

SET SESSION group_concat_max_len = 100000;
SET @sql = '';

SELECT GROUP_CONCAT(DISTINCT
                    CONCAT(
                            'IFNULL(IF(z_tmp_admin_system_settings.name = ''',
                            name,
                            ''', value, NULL), NULL) AS ',
                            name
                        )
           )
INTO @sql
FROM z_tmp_admin_system_settings;
SET @sql = CONCAT('SELECT subdomain, ', @sql, ' FROM name GROUP BY subdomain');

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

最佳答案

代码有几个问题-通过降低重力：
您需要选择from z_tmp_admin_system_settings，而不是from name
要分组的列称为category，而不是subdomain
由于查询的原则是使用聚合，因此您需要为生成的列使用聚合函数，例如MAX()；旧版本的MySQL允许不在非聚合列上使用聚合函数，但这不是一种习惯
如果某个列的名称与保留字冲突（在示例数据中不是这样，但可能不全面），最好用反勾号将列的名称括起来
DISTINCT可能不需要，除非每个类别都有重复的names（在这种情况下，可以将其添加回下面的代码）
旁注：IFNULL(..., NULL)是禁止操作
代码：

SET SESSION group_concat_max_len = 100000;
SET @sql = '';

SELECT GROUP_CONCAT(
    CONCAT('MAX(IF(z_tmp_admin_system_settings.name = ''', name, ''', value, NULL)) AS `', name, '`')
)
INTO @sql
FROM z_tmp_admin_system_settings;
SET @sql = CONCAT(
    'SELECT category, ',
    @sql,
    ' FROM z_tmp_admin_system_settings GROUP BY category'
);

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

Demo on DB Fiddle：

| category | 2fa | abc_processing_date | activate_new_approve_person | activate_new_schdule | additional_footer_for_person |
| -------- | --- | ------------------- | --------------------------- | -------------------- | ---------------------------- |
| acme     | 0   | today               | 1                           | 1                    |                              |