MySQL透视表查询带有动态列的截断键值 [英] MySQL pivot table query with dynamic columns truncating key value

问题描述

我正在尝试动态调整下表中的键值对.

I am trying to dynamically pivot the key value pairs in the table below.

+-----------------+-----------------------------+-------+
| 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 |   
+-----------------+-----------------------------+-------+

在下面运行查询时出现错误

Running my query below I am getting the error

'-new-schedule,IFNULL(IF(z_tmp_admin_system_settings.name = 'additional_footer_fo' at line 1

这是我使用Taryn在动态的MySQL数据透视表查询中描述的方法开发的列.

I this developed using method described by Taryn in 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;

推荐答案

您的代码存在几个问题-通过降低重力:

There are several problems with your code - by descreasing gravity:

• 您需要选择from z_tmp_admin_system_settings，而不是from name
• 要分组的列称为category，而不是subdomain
• 由于查询的原理是使用聚合，因此您需要为生成的列使用聚合函数，例如MAX()；旧版本的MySQL允许不对非聚合列使用聚合函数，但这不是习惯
• 如果列名中的一个与保留字发生冲突(在示例数据中不是这种情况，但可能不全面)，则最好在行名前后加上反引号 > 可能不需要
• DISTINCT，除非您为每个类别重复了name(在这种情况下，请随时将其添加回下面的代码中)
• 旁注:IFNULL(..., NULL)是禁止操作

• you need to select from z_tmp_admin_system_settings, not from name
• the column to group by is called category, not subdomain
• since the principle of the query is to use aggregation, you need an aggregate functions for the generated columns, such as MAX(); old versions of MySQL tolerate not using an aggregate function on non-aggregated columns, but that's not something to get accustomed to
• it is a good practice to surround the name of the columns with backticks, in case one of the name clashes with a reserved word (this is not the case in your sample data, but it is probably not comprehensive)
• DISTINCT is probably not needed, unless you have duplicated names per category (in this case, feel free to add it back to the below code)
• Side note: IFNULL(..., NULL) is a no-op

代码:

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;

DB Fiddle上的演示 :

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                    |                              |

这篇关于MySQL透视表查询带有动态列的截断键值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！