代码编织梦想

        一个sql中,union了几个子查询。单独执行每个子查询都没问题,但union后执行,报
ORA-00904: "xxx": invalid identifier

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



Sql代码
  1. SELECT supplier_city   
  2. FROM suppliers   
  3. WHERE supplier_name = 'IBM'  
  4. 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的几个子查询列名不同,如
 
Sql代码
  1. select supplier_id, supplier_name   
  2. from suppliers   
  3. UNION  
  4. select company_id, company_name   
  5. from companies   
  6. 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 使用列序号代替实际列名。如:
 
Sql代码
  1. select supplier_id, supplier_name   
  2. from suppliers   
  3. UNION  
  4. select company_id, company_name   
  5. from companies   
  6. ORDER BY 2;  
select supplier_id, supplier_name from suppliers UNION select company_id, company_name from companies ORDER BY 2;

2 为unoin的各个子查询使用相同的列名,如:
 
Sql代码
  1. select supplier_id as id, supplier_name as name  
  2. from suppliers   
  3. UNION  
  4. select company_id as id, company_name as name  
  5. from companies   
  6. 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子查询,都不会有问题。



 
I have two tables, TableA and TableB defined as follows,

TableA
A1 int
A2 int
A3 int

TableB
B1 int
B2 int
B3 int

If I try to run this query, SQL Server says syntex failed at the Order By clouse. Is such Order by not allowed in SQL, Any other way to achieve this?
Sql代码
  1. (Select A1, A2 from TableA)   
  2. Union All  
  3. (Select B1, B2 from TableB Order by B3)  
(Select A1, A2 from TableA) Union All (Select B1, B2 from TableB Order by B3)

Any help will be appreciated.

A:
引用
First of all, you can not order by a column that is not included in your SELECT list (我注:这句话是错误的;可以order by一个不在select列表中的column). Secondly, when performing a UNION query the ORDER BY clause must be (我注:not “must be”!) a column index not a column name, because a UNION query does not have column headings (although SQL Server(我注:此处泛指DBMS) pretends that it has by picking the column names used in the first query although this is not ANSI compliant]). Assuming you want to order the second column (A2 and B2) your query should look like this:
Code:
Sql代码
  1.    SELECT A1, A2   
  2.      FROM TableA   
  3. UNION ALL  
  4.    SELECT B1, B2   
  5.      FROM TableB   
  6.  ORDER BY 2  
 SELECT A1, A2 FROM TableA UNION ALL SELECT B1, B2 FROM TableB ORDER BY 2


Conceptually, ORDER BY works by producing the final query table with all the queries joined together (if it is a UNION query), then it orders the query results and does not care about what is in the database.
版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/dave_sunny/article/details/9158535

如何联合使用union和order by _iteye_3891的博客-爱代码爱编程

如果使用类似下面的SQL语句:select columnA, columnB from tableA where columnA = 'Condition 1'union select columnC, columnD from tableB where columnC = 'Condition 1'order by columnA asc 系统会报错:消

oracle中union语句中order by用法_xiyang5530的博客-爱代码爱编程

[color=red][b]union的各个子查询要有相同数量的列,且对应位置的列必须具有相同的数据类型;但列的名字可以不同。[/b][/color] [b][color=red]只能在union的最后一个子查询中使用order by[/color][/b],而这个order by是针对整个unioning后的结果集的。So: 如果unoin的几个子查询列

order by 详解-爱代码爱编程

日常开发中,我们经常会使用到order by,亲爱的小伙伴,你是否知道order by 的工作原理呢?order by的优化思路是怎样的呢?使用order by有哪些注意的问题呢?本文将跟大家一起来学习,攻克order by~ 一个使用order by 的简单例子 假设用一张员工表,表结构如下: CREATE TABLE `staff` ( `i

union与order by 结合使用_high...的博客-爱代码爱编程

  项目开发中遇到需要组合查询两张表返回分页列表,并需要采用创建时间排序,踩到一个小坑记录一下。 1.错误使用方法: 注:给两个子查询分别添加括号后sql不会报错,但是order by 没有效果,或者说没有达到预期 SELECT id, FILE_NAME, FILE_ID, CREATE_TIME FROM t_scf_confirm

order by排序语句的用法-爱代码爱编程

文章目录 学习连接语法用法示例1、按单个列的值排序2、按多个列的值排序3、按指定的规则排序4、按中文拼音字母顺序5、Order by和where条件共用 数据库中常用order