UNIONAll中ORDERBy的使⽤
⼀个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.
sql中union多表合并Sql代码
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 pri
ority 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的⼏个⼦查询列名不同，如
Sql代码
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 使⽤列序号代替实际列名。如：
Sql代码
select supplier_id, supplier_name
from suppliers
UNION
select company_id, company_name
from companies
ORDER BY 2;
2 为unoin的各个⼦查询使⽤相同的列名，如：
Sql代码:
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⼦查询，都不会有问题。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。