【SQL】两个带order by查询进行union all报ORA-00933错误的解决方法

这个是在工作中遇到的一个问题，后来仔细考虑了一下，应用了with。。as。。select的语法最终给解决了，记录一下。

 

在oracle SQL中，要求order by是select语句的最后一个语句，而且一个select语句中只允许出现一个order by语句，而且order by必须位于整个select语句的最后。

 

当时是要将一个十分复杂的检索明细查询和一个十分复杂的检索汇总查询的结果进行合并，以简化开发。

开发人员选择使用了union all来连接两个结果集。

※使用union all 而不用union来连接两个检索结果集的原因是，union实际上做了两部分动作：结果集合并+排序，而union all只进行结果集简单合并，不做排序。

 

我在这里简要做个模拟。

/////////////////////////////////////////////////////

//  1）两个表中的数据

/////////////////////////////////////////////////////

SQL> select * from tb1;

 

C1            C2

----- ----------

a1001          1

a1002          2

a1003          3

 

SQL> select * from tb2;

 

C1            C2

----- ----------

b2001          1

b2002          2

b2003          3

 

/////////////////////////////////////////////////////

//  2）两个带order by的查询

/////////////////////////////////////////////////////

SQL> select * from tb1 order by c1 desc;

 

C1            C2

----- ----------

a1003          3

a1002          2

a1001          1

 

SQL> select * from tb2 order by c1 desc;

 

C1            C2

----- ----------

b2003          3

b2002          2

b2001          1

 

/////////////////////////////////////////////////////

//  3）接下来，我们将两个查询的结果用union all合并起来

/////////////////////////////////////////////////////

//   可以看到 直接用union all连接两个子查询时，报出了ORA-00933错误

//   因为 oracle 认为第一个order by结束后整个select语句就该结束了，

//   但是发现后面没有逗号（；）或斜线（/）结束符，反而发现了 union all

/////////////////////////////////////////////////////

SQL> select * from tb1 order by c1 desc

  2  union all

  3  select * from tb2 order by c1 desc;

union all

*

第 2 行出现错误:

ORA-00933: SQL 命令未正确结束

 

/////////////////////////////////////////////////////

//  4）接下来，演示一下如何使用with。。as。。select。。

//     将两个查询的结果用union all合并而且能够执行

/////////////////////////////////////////////////////

SQL> with

  2  s1 as (

  3  select * from tb1 order by c1 desc

  4  ),

  5  s2 as (

  6  select * from tb2 order by c1 desc

  7  )

  8  select * from s1

  9  union all

 10  select * from s2;

 

C1            C2

----- ----------

a1003          3

a1002          2

a1001          1

b2003          3

b2002          2

b2001          1

 

已选择6行。