blocks|key|1034374|text|只需写|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1034375|Select+id,name,age
From+Student
Where+age+<+15
Union
Select+id,name,age
From+Student
Where+Name+like+"%25a%25"
Order+by+name|code-block|syntax|javascript|1034376|order+by将应用于完整的结果集|1034377|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Just write

<pre><code>Select id,name,age
From Student
Where age &lt; 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by name
</code></pre>

the order by is applied to the complete resultset

blocks|key|819225|text|Select+id,name,age
from
(
+++Select+id,name,age
+++From+Student
+++Where+age+<+15
++Union
+++Select+id,name,age
+++From+Student
+++Where+Name+like+"%25a%25"
)+results
order+by+name|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|819226|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>Select id,name,age
from
(
 Select id,name,age
 From Student
 Where age &lt; 15
 Union
 Select id,name,age
 From Student
 Where Name like "%a%"
) results
order by name
</code></pre>

blocks|key|819528|text|为了使排序只应用于UNION中的第一条语句，您可以将它放在带有UNION+ALL的子选择中(在Oracle中，这两种操作似乎都是必需的)：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|819529|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+"%25a%25"|code-block|syntax|javascript|819530|或者(处理Nicholas+Carey的评论)你可以保证顶部的SELECT是有序的，结果会出现在底部的SELECT上面，如下所示：|819531|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+"%25a%25"
Order+by+rowOrder,+name|819532|entityMap^0|9|5|V|9|0|0|V|6|1F|6|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]]|D|@]|E|$]]|$1|M|3|N|5|H|7|11|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|P|$]]

In order to make the sort apply to only the first statement in the <code>UNION</code>, you can put it in a subselect with <code>UNION ALL</code> (both of these appear to be necessary in Oracle):
<pre class="lang-sql prettyprint-override"><code>Select id,name,age FROM 
( 
 Select id,name,age
 From Student
 Where age &lt; 15
 Order by name
)
UNION ALL
Select id,name,age
From Student
Where Name like &quot;%a%&quot;
</code></pre>
Or (addressing Nicholas Carey's comment) you can guarantee the top <code>SELECT</code> is ordered and results appear above the bottom <code>SELECT</code> like this:
<pre class="lang-sql prettyprint-override"><code>Select id,name,age, 1 as rowOrder
From Student
Where age &lt; 15
UNION
Select id,name,age, 2 as rowOrder
From Student
Where Name like &quot;%a%&quot;
Order by rowOrder, name
</code></pre>

blocks|key|819340|text|其他两个答案都是正确的，但我认为值得注意的是，我陷入困境的地方是没有意识到您需要按别名排序，并确保两个选择的别名是相同的……所以|type|unstyled|depth|inlineStyleRanges|entityRanges|data|819341|select+'foo'
union
select+item+as+`foo`
from+myTable
order+by+`foo`|code-block|syntax|javascript|819342|请注意，我在第一个select中使用了单引号，而在其他select中使用了反引号。|819343|这将为您提供所需的排序。|819344|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Both 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
<pre><code>select 'foo'
union
select item as `foo`
from myTable
order by `foo`
</code></pre>
notice that I'm using single quotes in the first select but backticks for the others.
That will get you the sorting you need.

blocks|key|819404|text|Order+By在union之后应用，因此只需在语句的末尾添加一个order+by子句：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|819405|Select+id,name,age
From+Student
Where+age+<+15
Union
Select+id,name,age
From+Student
Where+Name+like+'%25a%25'
Order+By+name|code-block|syntax|javascript|819406|entityMap^0|0|8|9|5|X|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

<code>Order By</code> is applied after <code>union</code>, so just
add an <code>order by</code> clause at the end of the statements:

<pre><code>Select id,name,age
From Student
Where age &lt; 15
Union
Select id,name,age
From Student
Where Name like '%a%'
Order By name
</code></pre>

blocks|key|1034546|text|如果我希望排序仅应用于其中一个UNION+(如果使用UNION+ALL|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1034547|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+"%25a%25"
Order+by+name
)|code-block|syntax|javascript|1034548|entityMap^0|F|5|Q|9|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|S|8|@]|D|@]|E|$]]]|L|$]]

If I want the sort to be applied to only one of the <code>UNION</code> if use <code>UNION ALL</code>:
<pre><code>Select id,name,age
From Student
Where age &lt; 15
Union all
Select id,name,age
From 
(
Select id,name,age
From Student
Where Name like &quot;%a%&quot;
Order by name
)
</code></pre>

blocks|key|1034849|text|为了添加一个旧的主题，我使用了ROW_NUMBER+(使用MS+)。这允许在联合中进行排序(排序)。因此，使用@BATabNabber中的一个想法来分离Union的每一半，以及在select中包装整个东西的@Wodin，我得到了：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1034850|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+'%25a%25'
)+as+x
Order+by+Mainsort,+RowNumber|code-block|syntax|javascript|1034851|因此，调整或忽略您想要的排序依据，并按您认为合适的方式添加Descendings。|1034852|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/users/2340825/ba-tabnabber|1|https://stackoverflow.com/users/495319/wodin^0|1J|C|0|2V|6|1|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]|$A|Y|B|Z|1|10]]|C|$]]|$1|D|3|E|5|F|7|11|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|12|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|13|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]|S|$5|N|O|P|C|$Q|T]]]]

To add to an old topic, I used ROW_NUMBER (using MS SQL). This allows sorts (orders) within UNIONs.
So using an idea from <a href="https://stackoverflow.com/users/2340825/ba-tabnabber">@BATabNabber</a> to separate each half of the Union, and <a href="https://stackoverflow.com/users/495319/wodin">@Wodin</a> of wrapping the whole thing in a select, I got:
<pre><code>Select Id, Name, Age from
(
Select Id, Name, Age, 1 as Mainsort
 , ROW_NUMBER() over (order by age) as RowNumber
From Student
Where Age &lt; 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
</code></pre>
So adjust, or omit, what you want to Order by, and add Descendings as you see fit.

blocks|key|1034733|text|要将ORDER+BY或LIMIT子句应用于单个SELECT，请将SELECT括起来，并将子句放在括号内：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1034734|(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);|code-block|syntax|javascript|1034735|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

To apply an ORDER BY or LIMIT clause to an individual SELECT, parenthesize the SELECT and place the clause inside the parentheses:
<pre><code>(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);

</code></pre>

blocks|key|819584|text|可以使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|819585|Select+id,name,age
From+Student
Where+age+<+15
Union+ALL
SELECT+*+FROM+(Select+id,name,age
From+Student
Where+Name+like+"%25a%25")|code-block|syntax|javascript|819586|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Can use this:

<pre><code>Select id,name,age
From Student
Where age &lt; 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")
</code></pre>

blocks|key|1034791|text|为什么不使用TOP+X呢？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1034792|SELECT+pass1.*+FROM+
+(SELECT+TOP+2000000+tblA.ID,+tblA.CustomerName+
++FROM+TABLE_A+AS+tblA+ORDER+BY+2)+AS+pass1
UNION+ALL+
SELECT+pass2.*+FROM+
++(SELECT+TOP+2000000+tblB.ID,+tblB.CustomerName+
+++FROM+TABLE_B+AS+tblB+ORDER+BY+2)+AS+pass2|code-block|syntax|javascript|1034793|前2000000是一个任意的数字，其大小足以捕获所有数据。根据您的要求进行调整。|1034794|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Why not use TOP X?
<pre><code>SELECT pass1.* FROM 
 (SELECT TOP 2000000 tblA.ID, tblA.CustomerName 
 FROM TABLE_A AS tblA ORDER BY 2) AS pass1
UNION ALL 
SELECT pass2.* FROM 
 (SELECT TOP 2000000 tblB.ID, tblB.CustomerName 
 FROM TABLE_B AS tblB ORDER BY 2) AS pass2
</code></pre>
The TOP 2000000 is an arbitrary number, that is big enough to capture all of the data. Adjust as per your requirements.

Is it possible to order when the data is come from many select and union it together? Such as
<pre><code>Select id,name,age
From Student
Where age &lt; 15
Union
Select id,name,age
From Student
Where Name like &quot;%a%&quot;
</code></pre>
How can I order this query by name?
I tried this
<pre><code>Select id,name,age
From Student
Where age &lt; 15 or name like &quot;%a%&quot;
Order by name
</code></pre>
But that does not work.

How to order by with union in SQL?

当数据来自多个select并将其合并在一起时，是否可以进行排序？比如Select id,name,ageFrom StudentWhere age < 15UnionSelect id,name,ageFrom StudentWhere Name like "%a%"如何按名称对此查询进行排序？我试过了Select id,name,ageFrom StudentWhere age < 15 or

问如何在SQL中使用union进行排序？
EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中使用union进行排序？EN

回答 10

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中使用union进行排序？
EN