oracle 如何将 Order BY 放在 SQL UNION 中，以便 TOTALS 始终显示为最后一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1876643/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to put the Order BY in SQL UNION so TOTALS will show up always as last row?
提问by Roman Kagan
I have SQL UNION where second part of that statement is the row that represents TOTALS. How can I ORDER BY where TOTALS will ALWAYS will show up as the last row?
我有 SQL UNION，其中该语句的第二部分是代表 TOTALS 的行。我怎样才能 ORDER BY TOTALS 将始终显示为最后一行？
回答by Welbog
Add an extra column to the queries being UNIONed, and make that column the first column in your ORDER BY clause.
向要联合的查询添加额外的列，并使该列成为 ORDER BY 子句中的第一列。
So if I started with something like this:
所以如果我从这样的事情开始：
SELECT product, price
FROM table
UNION
SELECT 'Total' AS product, SUM(price)
FROM table
I'd add a new column like this:
我会添加一个这样的新列：
SELECT product, price
FROM (
SELECT product, price, 0 AS union_order
FROM table
UNION
SELECT 'Total' AS product, SUM(price), 1 AS union_order
FROM table
)
ORDER BY union_order
That way, regular products appear first, then the total appears at the end.
这样，常规产品首先出现，然后总数出现在最后。
回答by Mark Byers
Have you tried using GROUP BY ROLLUP- it might be just want you want, although it's difficult to tell when you haven't posted your query.
您是否尝试过使用GROUP BY ROLLUP- 它可能只是您想要的，尽管很难判断何时您还没有发布您的查询。
回答by tt83
You could try adding an 'order' column to each query and then order by that column...
您可以尝试向每个查询添加一个“订单”列，然后按该列排序...
SELECT x.*
FROM
(
SELECT columns, 2 AS [Order]
UNION
SELECT totals, 1 AS [Order]
) x
ORDER BY x.[Order]
回答by davek
select * from
(
select 1 as prio
, col1
, col2
...
from tableA
union
select 2 as prio
, totalCol1
, totalCol2
...
from tableB
) order by prio
