如何在 SQL 中使用 union 排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4715820/
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 order by with union in SQL?
提问by Guilgamos
Is it possible to order when the data is come from many select and union it together? Such as
当数据来自多个选择并将其合并在一起时，是否可以进行排序？如
Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
How can I order this query by name.
如何按名称订购此查询。
Some said you can query look like this.
有人说你可以像这样查询。
Select id,name,age
From Student
Where age < 15 or name like "%a%"
Order by name
But in this case I just ignore that solution.
但在这种情况下，我只是忽略了该解决方案。
回答by bernd_k
Just write
写就好了
Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name
the order by is applied to the complete resultset
order by 应用于完整的结果集
回答by Mark Robinson
Select id,name,age
from
(
Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
) results
order by name
回答by BA TabNabber
In order to make the sort apply to only the first statement in the UNION, you can put it in a subselect with UNION ALL (both of these appear to be necessary in Oracle):
为了使排序仅适用于 UNION 中的第一条语句，您可以将其放入带有 UNION ALL 的子选择中（这两个在 Oracle 中似乎都是必需的）：
Select id,name,age FROM
(
Select id,name,age
From Student
Where age < 15
Order by name
)
UNION ALL
Select id,name,age
From Student
Where Name like "%a%"
Or (addressing Nicholas Carey's comment) you can guarantee the top SELECT is ordered and results appear above the bottom SELECT like this:
或者（针对 Nicholas Carey 的评论）您可以保证顶部 SELECT 是有序的，并且结果显示在底部 SELECT 上方，如下所示：
Select id,name,age, 1 as rowOrder
From Student
Where age < 15
UNION
Select id,name,age, 2 as rowOrder
From Student
Where Name like "%a%"
Order by rowOrder, name
回答by Yevgeny Simkin
Both the other answers are correct, but I thought it worth noting that the place where I got stuck was not realizing that you'll need order by the alias and make sure that the alias is the same for both the selects... so
其他两个答案都是正确的，但我认为值得注意的是，我被卡住的地方没有意识到您需要按别名排序并确保两个选择的别名相同......所以
select 'foo'
union
select item as `foo`
from myTable
order by `foo`
notice that I'm using single quotes in the first select but backticks for the others.
请注意，我在第一个选择中使用单引号，但在其他选择中使用反引号。
That will get you the sorting you need.
这将为您提供所需的排序。
回答by Sunil Kumar
Order Byis applied after union, so just
add an order byclause at the end of the statements:
Order By在 之后应用union，因此只需order by在语句末尾添加一个子句：
Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like '%a%'
Order By name
回答by osydorchuk
If I want the sort to be applied to only one of the UNION if use Union all:
如果我希望排序仅应用于 UNION 之一，如果使用 Union all：
Select id,name,age
From Student
Where age < 15
Union all
Select id,name,age
From
(
Select id,name,age
From Student
Where Name like "%a%"
Order by name
)
回答by Mandrake
As other answers stated , 'Order by' after LAST Union should apply to both datasets joined by union.
正如其他答案所述，LAST Union 之后的“Order by”应该适用于由 union 连接的两个数据集。
I was having two data sets but using different tables but same columns. 'Order by' after LAST Union didn't still worked. Using ALIAS for column used in 'order by' did the trick.
我有两个数据集，但使用不同的表但列相同。LAST Union 之后的“Order by”仍然无效。对“order by”中使用的列使用 ALIAS 可以解决问题。
Select Name, Address for Employee
Union
Select Customer_Name, Address from Customer
order by customer_name; --Won't work
So solution is use Alias 'User_Name' :
所以解决方案是使用别名 'User_Name' ：
Select Name as User_Name, Address for Employee
Union
Select Customer_Name as User_Name, Address from Customer
order by User_Name;
回答by Ahmad Aghazadeh
Can use this:
可以用这个：
Select id,name,age
From Student
Where age < 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")
