mysql - 如何排序这个 MySQL 查询
问题描述
我正在尝试在此查询中插入 ORDER BY 以对 Total 列进行排序:
SELECT GROUP_CONCAT(CONCAT('SELECT ''', COLUMN_NAME,''' MyColumns, SUM(`', COLUMN_NAME,'`) Total FROM mydb.source_table') SEPARATOR '\n UNION ALL \n')
INTO @sql
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'source_table'
AND COLUMN_NAME NOT IN ('ID', 'Name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
结果如下:
+-----------+-----------+
| MyColumns | Total |
+-----------+-----------+
| ABC | 67.00 |
+-----------+-----------+
| DEF | 40.00 |
+-----------+-----------+
| GHI | 33.00 |
+-----------+-----------+
| JKL | 39.00 |
+-----------+-----------+
| MNO | 33.00 |
+-----------+-----------+
这是我到目前为止尝试过的,但没有运气:
1. mydb.source_table ORDER BY Total
2. SEPARATOR '\nUNION ALL\n' ORDER BY 'Total' ASC
3. SEPARATOR '\nUNION ALL\n ORDER BY Total' ASC
4. AND COLUMN_NAME NOT IN ('ID', 'Name') ORDER BY 'Total' ASC;
5. ORDER BY 'Total' ASC SEPARATOR '\nUNION ALL\n'
解决方案
I think you want:
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');
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()
.
Also please note that you don't need CONCAT()
within GROUP_CONCAT()
: MySQL does that by default already.
推荐阅读
- php - Laravel URL 重定向回来
- apache-kafka - KStreams - 如何处理一个主题的消息延迟
- javascript - 将 A Class Name 更改为空名称
- python - spyder-error:('HY000', '[HY000] [Teradata][ODBC Teradata Driver] (77) 需要 Visual C++ Redistributable 2012 Update 4。(77) (SQLDriverConnect)')
- c# - 在 dotnet 中创建不可编辑的模型属性
- css - 当它也是一个弹性容器时,如何防止最后一个弹性项目填充剩余宽度
- java - 与服务连接时缺少客户端证书链问题
- reactjs - reactjs中如何处理POST请求?
- python - 转换为 numpy 数组时,数组中的值会发生变化
- function - 雪花中的功能数字等价物是什么?