更新时间:2023-02-04 14:31:49
我认为您想要:
SELECT CONCAT(
GROUP_CONCAT(
'SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total FROM mydb.source_table'
SEPARATOR '\n UNION ALL \n'
),
'\nORDER BY Total DESC'
)
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'source_table'
AND COLUMN_NAME NOT IN ('ID', 'Name');
合理价格:ORDER BY
子句应放在所有UNION ALL
子查询之后 -因此它必须在GROUP_CONCAT()
之外,在外部CONCAT()
中.
Rationale: the ORDER BY
clause should go after all UNION ALL
subqueries - so it needs to be outside of the GROUP_CONCAT()
, in an outer CONCAT()
.
还请注意,您不需要CONCAT()
GROUP_CONCAT()
:MySQL默认已经做到了.
Also please note that you don't need CONCAT()
within GROUP_CONCAT()
: MySQL does that by default already.