同时使用Union和Order by问题(ORA-00933错误)解决
程序员文章站
2022-05-02 12:17:29
之前，同事在编写视图的过程中遇到这样了这个错误。我把简化后的语句整理如下： 1: select 2: '2016' as nf, 3: qxdm, 4: round(sum(tbdlmj)/10000,2) as csydmj--单位转换，2位小数 5: from dltb_2016@dblink_... ......
之前，同事在编写视图的过程中遇到这样了这个错误。我把简化后的语句整理如下：
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;