ORACLE数据库--order by----ORA-01785 ORDER BY必须是select-list 表达式的数目

sql语句真是博大精深啊！

下面是报错的sql语句：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where '1582443083000' = '1582443083000'
   and cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and lb.ruleid <> '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       max(lb.sid),
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
   and rownum=1
   group by  lb.contno,
       cont.conttype, cont.appntno, cont.insuredno,
      cont.appntname, cont.insuredname,
       lb.create_time,
       lb.sum_total,
       lb.sum_price,
       lb.sum_tax,      
       lb.managecom
   order by  max(lb.sid) desc
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and not exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'

　　运行之后：ORA-01785  ORDER BY必须是select-list 表达式的数目

我理解的是多个union连接的时候是不能够使用order by进行排序的，因为他不可能按照每一块给你去单独的进行排序。因为有很多主的查询语句。当然如果只有一个主的查询语句是可以的

接下来，我进行了改造，发现还是不行：报ora-00907缺失右括号

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where '1582443083000' = '1582443083000'
   and cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and lb.ruleid <> '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
   and lb.sid=(select sid from lis_busi_transactions where contno=lb.contno and rownum=1 order by sid)
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and not exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2024-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'

　　原因还是，

?
1
and lb.sid=(select sid from lis_busi_transactions where contno=lb.contno and rownum=1 order by sid)  这样写还是不能够按照你想象中的场景进行排序的。（具体原因自己也不是很清楚）下面这种写法是可以的：
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where '1582439926000' = '1582439926000'
   and cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and lb.ruleid <> '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date
 '2020-02-23'
   and to_date(lb.create_time) <= date '2023-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
 
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date
 '2020-02-23'
   and to_date(lb.create_time) <= date
 '2023-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'
      
   and lb.sid = (select max(sid) from lis_busi_transactions where contno=lb.contno and ruleid='16'   )
  
/* group by lb.contno,
          lb.managecom,
          cont.conttype,
          cont.appntno,
          cont.insuredno,
          cont.appntname,
          cont.insuredname,
          lb.create_time,
          lb.sum_total,
          lb.sum_price,
          lb.sum_tax*/
 
union
select lb.contno,
       decode(cont.conttype, '1', cont.appntno, '2', cont.insuredno),
       decode(cont.conttype, '1', cont.appntname, '2', cont.insuredname),
       to_date(lb.create_time),
       to_char(lb.sum_total, '9999999990.99'),
       to_char(lb.sum_price, '9999999990.99'),
       to_char(lb.sum_tax, '9999999990.99'),
       lb.sid,
       lb.managecom
  from LIS_BUSI_TRANSACTIONS lb, lccont cont
 where cont.contno = lb.contno
   and lb.invoiceflag in ('00')
   and lb.successflag = '1'
   and not exists
 (select 1 from lcgrpcont c where c.grpcontno = lb.contno)
   and lb.sum_total > 0
   and (cont.currency = '01' or cont.currency is null)
   and not exists (select 1
          from ljagetendorse a, LIS_BUSI_TRANSACTIONS b
         where a.actugetno = b.sourceid
           and a.getflag = '1'
           and b.ruleid in ('3', '6', '10', '12')
           and a.actugetno = lb.sourceid
           and b.sid = lb.sid)
   and exists (SELECT 1 FROM ljapay d where d.otherno = cont.contno)
   and not exists (select 1
          from lpedoritem
         where contno = lb.contno
           and edorstate = '0'
           and edortype = 'PT')
   and lb.ruleid = '16'
   and lb.contno = 'P442011830004'
   and to_date(lb.create_time) >= date'2020-02-23'
   and to_date(lb.create_time) <= date'2023-02-23'
   and lb.managecom like '86%'
   and lb.managecom like '86%'

　　

 

posted on   ~码铃薯~  阅读(3046)  评论(0)  编辑  收藏  举报

编辑推荐：
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
阅读排行：
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY

导航

< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示