如何与工会在SQL排序?

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

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

但这并不奏效。

当前回答

只写

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

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

其他回答

可以用这个:

Select id,name,age
From Student
Where age < 15
Union ALL
SELECT * FROM (Select id,name,age
From Student
Where Name like "%a%")

顺序顺序应用在并集之后，所以在语句的末尾添加一个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

只写

Select id,name,age
From Student
Where age < 15
Union
Select id,name,age
From Student
Where Name like "%a%"
Order by 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;

要将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);

推荐文章