17,089
社区成员
发帖
与我相关
我的任务
分享
这种情况出现的几率不大,如果有先按STARTNO列排序后再STARTNO-ROWNUM就好了。
[Quote=引用 11 楼 wfqqwer5213 的回复:]
DROP TABLE ZS_BILLINFO;
CREATE TABLE ZS_BILLINFO(STARTNO VARCHAR2(10));
SELECT * FROM ZS_BILLINFO;
STARTNO
1 00001
2 00002
3 00003
4 00004
5 00006
6 00007
7 00008
8 00009
9 00011
10 00012
SELECT MIN(STARTNO) STARTNO, MAX(STARTNO) ENDNO
FROM (SELECT STARTNO, STARTNO - ROWNUM RN
FROM ZS_BILLINFO
ORDER BY STARTNO)
GROUP BY RN
ORDER BY RN;
--try it:
select sum(金额) from zs_billinfo where startno>='00001' and startno<='00004' union
(select sum(金额) from zs_billinfo where startno>='00006' and startno<='00009') union
(select sum(金额) from zs_billinfo where startno>='00011' and startno<='10000');
SELECT MIN (aaa.startno) startno, MAX (aaa.startno) endno
FROM (SELECT aa.*, SUM (aa.flag) OVER (ORDER BY aa.startno) flag_1
FROM (SELECT a.*,
DECODE
( LEAD (TO_NUMBER (startno),
1,
TO_NUMBER (startno) + 1
) OVER (ORDER BY a.startno)
- TO_NUMBER (startno),
1, 0,
1
) flag
FROM zs_billinfo a) aa)
GROUP BY aaa.flag_1
SQL> select * from ZS_BILLINFO;
STARTNO
----------
00001
00002
00003
00004
00006
00007
00008
00009
00011
00012
10 rows selected
SQL>
SQL> select min(startno) startno, max(startno) endno
2 from ZS_BILLINFO
3 group by (startno - rownum)
4 order by 1;
STARTNO ENDNO
---------- ----------
00001 00004
00006 00009
00011 00012
-- 连续数问题:
SQL> SELECT MIN(STARTNO) STARTNO,
2 MAX(STARTNO) ENDNO
3 FROM (
4 SELECT STARTNO,
5 STARTNO - ROWNUM RN
6 FROM TABLE_NAME TT
7 )YY
8 GROUP BY RN;
STARTNO ENDNO
------- -----
00001 00004
00006 00009
00011 00012
[Quote=引用楼主 zhbl 的帖子:]SQL> select * from ZS_BILLINFO;
STARTNO
----------
1
2
3
4
6
7
8
9
11
12
10 rows selected
SQL>
SQL> select min(startno) startno, max(startno) endno
2 from ZS_BILLINFO
3 group by (startno - rownum)
4 order by 1;
STARTNO ENDNO
---------- ----------
1 4
6 9
11 12