我有一个执收码表(zs_billinfo),里面有个票据号码字段(startno)比如内容是 00001到10000啥的,但是中间有些票据号码缺少, 比如少00005,00010等,我现在需要把它按号码段统计出来。就是00001-00004为一段,00006-00009为一段,00011-10000为一段(因为我需要根据这些票段来统计金额,数量什么的东西),这个sql有没有办法做到呢?
原表格式
startno
00001
00002
00003
00004
00006
00007
00008
00009
00011
00012
...现在表格式
startno endno
00001 00004
00006 00009
00011 10000
原表格式
startno
00001
00002
00003
00004
00006
00007
00008
00009
00011
00012
...现在表格式
startno endno
00001 00004
00006 00009
00011 10000
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
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
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
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
--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');
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;
rownum no
1 1
2 3
3 6
4 7
5 10
. .
. .
. .
100 103按照LS各位的思路
6到103都成一组了!