如何与工会订购

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.

但在这种情况下，我忽略了这个解。

Thank you in advance.

提前谢谢你。

6 个解决方案

#1

193

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应用于完整的resultset

#2

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

#3

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):

为了使sort只适用于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 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

#4

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.

这会得到你需要的排序。

#5

Order By is applied after union, so just add an order by clause 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

#6

If I want the sort to be applied to only one of the UNION if use Union all:

如果我想要这种类型只应用于联盟中的一个，如果使用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
)

#1

193