如何对这个MySQL查询进行排序 [英] How to Sort this MySQL Query

问题描述

我正在尝试在此查询中插入ORDER BY来对总计"列进行排序:

I am trying to insert the ORDER BY in this query to sort the Total column:

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

这是我到目前为止尝试过的，但是没有运气:

Here's what I have tried so far but no luck:

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'

推荐答案

我认为您想要:

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.

这篇关于如何对这个MySQL查询进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！