uinon all 和 order by问题总结

SQL UNION 和UNION ALL 操作符

    Reference: http://www.w3school.com.cn/sql/sql_union.asp

SQL UNION 操作符

    UNION 操作符用于合并两个或多个 SELECT语句的结果集。

    请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型，但列名可以不同。同时，每条 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

    注释：默认地，UNION操作符选取不同的值。如果允许重复的值，请使用UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

    另外，UNION 结果集中的列名总是等于 UNION 中第一个SELECT 语句中的列名。

    下面的例子中使用的原始表：

Employees_China:

E_ID	E_Name
01	Zhang, Hua
02	Wang, Wei
03	Carter, Thomas
04	Yang, Ming

Employees_USA:

E_ID	E_Name
01	Adams, John
02	Bush, George
03	Carter, Thomas
04	Gates, Bill

 

使用 UNION 命令

实例

    列出所有在中国和美国的 不同的雇员名：

SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA

结果

E_Name

Zhang, Hua

Wang, Wei

Carter, Thomas

Yang, Ming

Adams, John

Bush, George

Gates, Bill

    注释：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION 命令只会选取不同的值。

UNION ALL

    UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

SQL Statement 1
UNION ALL
SQL Statement 2

使用 UNION ALL 命令

实例:

    列出在中国和美国的所有的雇员：

SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA

结果

E_Name

Zhang, Hua

Wang, Wei

Carter, Thomas

Yang, Ming

Adams, John

Bush, George

Carter, Thomas

Gates, Bill

union all 和order by

    Reference: http://www.linuxidc.com/Linux/2011-05/35796.htm  union all 和 order by

    有时候，我们会将经过排序(order by)后的结果集与其他经过排序的结果集进行合并(union or union all)，比如：    

select * from tb where length(id)=5 order by id desc     
union all     
select * from tb where length(id)=10 order by id asc     

    通常情况下，上面的查询将会得到下面的错误提示:    
ORA-00933: SQL command not properly ended    
    错误指向union关键字这里。

    下面我们来看一个具体的 实例:         

create table t as     
select 'china' col_1,'america' col_2,'canada' col_3,-1 status from dual union all     
select '花生','瓜子','绿豆',0 from dual union all     
select '牙膏','牙刷','杯子',3 from dual union all     
select '芍药','牡丹','月季',1 from dual union all     
select '优乐美','香飘飘','炸鸡',2 from dual       

    需求：有如上表t，status字段的取值范围:[-1,3]，我们想要做的是，按照这样的方式排序0,1,2,3,-1    
    解法：更具题义，我们需要将status分为两个区域(>0 和<0) ，然后分别对每一个区域内的数据进行order by排序 ，于是有下面的查询    

select col_1,col_2,col_3,status     
from t     
where status >= 0      
order by status  --1     
union     
select col_1,col_2,col_3,status     
from t     
where status < 0     
order by status  --2     

    不幸的是，正如刚刚开始时我提示的一样，我们得到了下面的错误提示:    
ORA-00933: SQL command not properly ended    
    如果将第一个select语句的order by子句去掉，得到的又不是我们想要的结果，此时得到的结果是将整个结果集进行排序。如果将两个排序子句都去掉的话，虽然按照status为正负数分开了，但是没有排序    
    下面我们来看看 正确的答案吧!  
解法一:   如果是union all前后分别排序的话，在外面包一层select就起作用了。

<span style="background-color: rgb(255, 255, 0);">select * from ( </span>    
       select col_1,col_2,col_3,status     
       from t     
       where status >= 0     
       order by status)     
union all     
<span style="background-color: rgb(255, 255, 0);">select * from (</span>     
       select col_1,col_2,col_3,status     
       from t     
       where status < 0     
       order by status)     

结果：  
COL_1  COL_2  COL_3      STATUS    
------       -------    ------        ----------    
花生       瓜子    绿豆           0    
芍药       牡丹    月季           1    
优乐美  香飘飘  炸鸡          2    
牙膏       牙刷    杯子           3    
china     americacanada      -1   

解法二:  

select * from t    
order by    
      decode(status,   
             -1,1,   
             3,2,   
             2,3,   
             1,4,   
             0,5) desc   

    这可是一个很妙的排序，本人首次看到在order by语句中可以使用decode()函数来排序。

    同理，我们也可以使用case语句来排序:  

解法三:  

select * from t    
order by    
      case status   
      when -1 then 5   
      when 3 then 4    
      when 2 then 3    
      when 1 then 2    
      else 1   
      end    

    最后，union和unionall中都支持orderby和groupby排序和分组子句 

    union all 和order by, 两个连用的话会是，查看下一段资料。

另外一段资料：union all and order by

    这段资料里面涉及到的英文资料的链接很好。

    Reference: http://blog.sina.com.cn/s/blog_6cb0deff0100t4l8.html

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

关于union的使用：
    SQL: UNION Query：
http://www.techonthenet.com/sql/union.php
    SQL: UNION ALL Query：
http://www.techonthenet.com/sql/union_all.php

    前面已经提到：union的各个子查询要有相同数量的列，且对应位置的列必须具有相同的数据类型；但列的名字可以不同。
    thediffrence between UNION ALL and UNION is that UNION will attempt to eliminateduplicates.

关于order by的使用：
    SQL: ORDER BY Clause
http://www.techonthenet.com/sql/order_by.php
    Example #3
    You can also sort by relative position in the result set, where the first fieldin 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;  

    This wouldreturn all records sorted by the supplier_city field in descending order, sincethe 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 effectivelygot lower binding priority than the UNION). 
    The ORDER BY clause just needs to be the last statement, after you've done allyour unioning. You can union several sets together, then put an ORDER BY clause after the last set.
    所以，只能在union的最后一个子查询中使用order by，而这个order by是针对整个union后的结果集的。So：
    如果unoin的几个子查询列名不同，如

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

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

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

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

solution#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;  

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

Q&A:
http://p2p.wrox.com/sql-language/9505-order-union.html
Q:

引用

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?

 (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 ANSIcompliant]). Assuming you want to order the second column (A2 and B2) your query should look like this:
Code:

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.