同時使用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錯誤。

檢查了列的數量、數據格式均保持一致，沒有不對應的現象。

追查了一下原因，最終發現是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;