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

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

   1: select
   2: '2016' as nf,
   3: qxdm,
   4: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
   5: from dltb_2016@dblink_td_tdxz m where dlmc='城市'
   6: group by m.qxdm order by m.qxdm
   7:  
   8: union all 
   9:  
  10: select
  11: '2017' as nf,
  12: qxdm,
  13: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
  14: from dltb_2017@dblink_td_tdxz n where dlmc='城市'
  15: group by n.qxdm order by n.qxdm

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

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

image

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

最终处理方式参考如下:

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

   1: select
   2: '2016' as nf,
   3: qxdm,
   4: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
   5: from dltb_2016@dblink_td_tdxz m where dlmc='城市'
   6: group by m.qxdm 
   7:  
   8: union all 
   9:  
  10: select
  11: '2017' as nf,
  12: qxdm,
  13: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
  14: from dltb_2017@dblink_td_tdxz n where dlmc='城市'
  15: group by n.qxdm

或者

   1: select
   2: '2016' as nf,
   3: qxdm,
   4: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
   5: from dltb_2016@dblink_td_tdxz m where dlmc='城市'
   6: group by m.qxdm 
   7:  
   8: union all 
   9:  
  10: select
  11: '2017' as nf,
  12: qxdm,
  13: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
  14: from dltb_2017@dblink_td_tdxz n where dlmc='城市'
  15: group by n.qxdm order by qxdm

2、可以再嵌套一层查询

   1: select * from (
   2: select
   3: '2016' as nf,
   4: qxdm,
   5: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
   6: from dltb_2016@dblink_td_tdxz m where dlmc='城市'
   7: group by m.qxdm order by m.qxdm
   8: )
   9:  
  10: union all 
  11:  
  12: select * from (
  13: select
  14: '2017' as nf,
  15: qxdm,
  16: round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
  17: from dltb_2017@dblink_td_tdxz n where dlmc='城市'
  18: group by n.qxdm order by n.qxdm
  19: )

或者

   1: with
   2:  s1 as (
   3:  select
   4:        '2016' as nf,
   5:        qxdm,
   6:        round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
   7:        from dltb_2016@dblink_td_tdxz m where dlmc='城市'
   8:        group by m.qxdm order by m.qxdm
   9:   ),
  10:   s2 as (
  11:   select
  12:      '2017' as nf,
  13:      qxdm,
  14:      round(sum(tbdlmj)/10000,2) as csydmj--单位转换,2位小数
  15:      from dltb_2017@dblink_td_tdxz n where dlmc='城市'
  16:      group by n.qxdm order by n.qxdm
  17:   )
  18:   select * from s1
  19:   union all
  20:   select * from s2;

posted on 2019-01-23 16:22  jingkunliu  阅读(808)  评论(1编辑  收藏  举报

导航