更新时间:2021-12-26 01:25:25
您的代码存在几个问题-通过降低重力:
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)
是禁止操作from z_tmp_admin_system_settings
, not from name
category
, not subdomain
MAX()
; old versions of MySQL tolerate not using an aggregate function on non-aggregated columns, but that's not something to get accustomed toDISTINCT
is probably not needed, unless you have duplicated name
s per category (in this case, feel free to add it back to the below code)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;
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 | |