UNION中ORDER By的使用

UNION中ORDER By的使用

文章分类:数据库 遇到的问题：
一个sql中，union了几个子查询。单独执行每个子查询都没问题，但union后执行，报
ORA-00904: "xxx": invalid identifier

关于union的使用：
SQL: UNION Query：

SQL: UNION ALL Query：

所union的各个子查询要有相同数量的列，且对应位置的列必须具有相同的数据类型；但列的名字可以不同。
the diffrence between UNION ALL and UNION is that UNION will attempt to eliminate duplicates.


关于order by的使用：
SQL: ORDER BY Clause

Example #3
You can also sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'IBM'
ORDER BY 1 DESC;

This would return all records sorted by the supplier_city field in descending order, since the supplier_city field is in position #1 in the result set.



union中order by的使用：
You have to use the Order By at the end of ALL the unions。
the ORDER BY is considered to apply to the whole UNION result(it's effectively got lower binding priority than the UNION). 
The ORDER BY clause just needs to be the last statement, after you've done all your unioning. You can union several sets together, then put an ORDER BY clause after the last set.
所以，只能在union的最后一个子查询中使用order by，而这个order by是针对整个unioning后的结果集的。So：
如果unoin的几个子查询列名不同，如

select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY ？;

select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY ？;

这里的问号如果是company_name，则执行整个查询会报“company_name：invalid identifier”（当然，单独执行第二个含order by的子查询是没有问题的）；这是因为unioning后结果集的列名是以第一个参加union的子查询的列名为准的；order by针对的是整个unioning后的结果集。对整个查询结果来说，无”company_name“这个字段
如果是supplier_name，则单独执行第二个含order by的子查询是会报“supplier_name：invalid identifier”的，而执行整个查询是没有问题的，因为order by针对的是unioning后的整个结果集，而这“整个结果集”是有supplier_name这列的（以第一个union子查询的列名作为 unioning后整个结果集的列名）

为了避免这样事情的发生，可以：
1 使用列序号代替实际列名。如：

select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;

select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;

2 为unoin的各个子查询使用相同的列名，如：

select supplier_id as id, supplier_name as name
from suppliers
UNION
select company_id as id, company_name as name
from companies
ORDER BY name;

select supplier_id as id, supplier_name as name
from suppliers
UNION
select company_id as id, company_name as name
from companies
ORDER BY name;

这样，不管是执行整个查询还是单独执行包含order by的最后一个union子查询，都不会有问题。