同时使用Union和Order by问题(ORA-00933错误)解决

之前，同事在编写视图的过程中遇到这样了这个错误。我把简化后的语句整理如下：

select
'2016' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2016@dblink_td_tdxz m where dlmc='城市'
group by m.qxdm order by m.qxdm

union all

select
'2017' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2017@dblink_td_tdxz n where dlmc='城市'
group by n.qxdm order by n.qxdm

主要是查询各个管辖区中2016年和2017年地类图斑数据中城市用地的面积，语句分单块均可以执行成功，但是使用UNION后则出现ora-00933错误。

检查了列的数量、数据格式均保持一致，没有不对应的现象。

追查了一下原因，最终发现是union和order by字句引起的。

最终处理方式参考如下：

1、如果排序没必要，可以直接去掉，或者在union后统一排序

select
'2016' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2016@dblink_td_tdxz m where dlmc='城市'
group by m.qxdm

union all

select
'2017' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2017@dblink_td_tdxz n where dlmc='城市'
group by n.qxdm

或者

select
'2016' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2016@dblink_td_tdxz m where dlmc='城市'
group by m.qxdm

union all

select
'2017' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2017@dblink_td_tdxz n where dlmc='城市'
group by n.qxdm order by qxdm

2、可以再嵌套一层查询

select * from (
select
'2016' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2016@dblink_td_tdxz m where dlmc='城市'
group by m.qxdm order by m.qxdm
)

union all

select * from (
select
'2017' as nf,
qxdm,
round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
from dltb_2017@dblink_td_tdxz n where dlmc='城市'
group by n.qxdm order by n.qxdm
)

或者

with
 s1 as (
 select
       '2016' as nf,
       qxdm,
       round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
       from dltb_2016@dblink_td_tdxz m where dlmc='城市'
       group by m.qxdm order by m.qxdm
  ),
  s2 as (
  select
     '2017' as nf,
     qxdm,
     round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数
     from dltb_2017@dblink_td_tdxz n where dlmc='城市'
     group by n.qxdm order by n.qxdm
  )
  select * from s1
  union all
  select * from s2;