当数据来自许多选择和联合在一起时,是否有可能进行排序?如

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"

如何按名称对此查询进行排序?

我试过了

Select id,name,age
From Student
Where age < 15 or name like "%a%"
Order by name

但这并不奏效。


当前回答

为了使排序只适用于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%"

或者(针对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

其他回答

正如其他答案所述,在最后一个UNION之后的ORDER BY应该适用于由UNION连接的两个数据集。

我有两个使用不同表但相同列的数据集。上一个联盟之后的命令仍然没有起作用。

为ORDER BY子句中使用的列使用别名可以达到目的。

SELECT Name, Address FROM Employee 
UNION
SELECT Customer_Name, Address FROM Customer
ORDER BY customer_name;   --Won't work

解决方案是使用别名User_Name,如下所示:

SELECT Name AS User_Name, Address FROM Employee 
UNION
SELECT Customer_Name AS User_Name, Address FROM Customer
ORDER BY User_Name; 
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

要将ORDER BY或LIMIT子句应用于单个SELECT,请将SELECT子句插入圆括号内:

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

只写

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name

顺序被应用到完整的结果集

为了添加到旧的主题,我使用了ROW_NUMBER(使用MS SQL)。这允许在union内排序(顺序)。 所以使用来自@BATabNabber的想法来分离联盟的每一半,而@Wodin将整个事情包装在一个选择中,我得到:

Select Id, Name, Age from
(
Select Id, Name, Age, 1 as Mainsort
 , ROW_NUMBER() over (order by age) as RowNumber
From Student
Where Age < 15

Union

Select Id, Name, Age, 2 as Mainsort
 , ROW_NUMBER() over (Order by Name) as RowNumber
From Student
Where Name like '%a%'
) as x
Order by Mainsort, RowNumber

所以调整,或省略,你想要的顺序,并添加下降,因为你认为合适。