oracle中断号的取得
1.根据号码求出号段
-- WITH t AS ( SELECT '1' tid,'0001' num FROM DUAL UNION ALL SELECT '1' tid,'0002' num FROM DUAL UNION ALL SELECT '1' tid,'0003' num FROM DUAL UNION ALL SELECT '1' tid,'0005' num FROM DUAL UNION ALL SELECT '1' tid,'0007' num FROM DUAL UNION ALL SELECT '2' tid,'0011' num FROM DUAL UNION ALL SELECT '2' tid,'0012' num FROM DUAL UNION ALL SELECT '2' tid,'0023' num FROM DUAL UNION ALL SELECT '2' tid,'0035' num FROM DUAL UNION ALL SELECT '1' tid,'0008' num FROM DUAL UNION ALL SELECT '2' tid,'0036' num FROM DUAL)-- 表的数据TID NUM--- ----1 00011 00021 00031 00051 00072 00112 00122 00232 00351 00082 0036-- 期望结果：TID MINNUM MAXNUM--- ------ ------1 0001 00031 0005 00051 0007 00082 0011 00122 0023 00232 0035 0036-- SQL1:SELECT n.tid,MIN(n.num) minnum,MAX(n.num) maxnum FROM ( SELECT m.tid, m.num, m.num - ROWNUM group_num FROM (SELECT t.tid, t.num FROM t ORDER BY t.tid, t.num) m) nGROUP BY n.tid,n.group_numORDER BY 1,2-- SQL2:
select t.* from tb t
sn
1
2
4
6
8
13
14
SELECT DISTINCT s + LEVEL - 1 miss_no FROM (SELECT lag(sn, 1) over(ORDER BY sn) + 1 s, sn - 1 e FROM tb) START WITH e - s >= 0CONNECT BY LEVEL <= e - s + 1 ORDER BY 1;
结果：
miss_no
3
5
7
9
10
11
12
SQL> WITH tb AS ( 2 SELECT 201102 month_id,0 company_id,0 type_id,500 plan_amount FROM DUAL UNION ALL 3 SELECT 201111 month_id,2 company_id,2 type_id,600 plan_amount FROM DUAL 4 ) 5 SELECT TO_DATE(t1.month_id, 'yyyymm') + t2.rn - 1 every_day, 6 t1.company_id, 7 t1.type_id, 8 plan_amount / ((LAST_DAY(TO_DATE(t1.month_id, 'yyyymm')) - 9 TO_DATE(t1.month_id, 'yyyymm')) + 1) avg_amount 10 FROM tb t1, 11 (SELECT ROWNUM rn, 12 lastday 13 FROM (SELECT MAX(LAST_DAY(TO_DATE(tb.month_id, 'yyyymm')) - 14 TO_DATE(tb.month_id, 'yyyymm')) + 1 lastday 15 FROM tb) 16 CONNECT BY ROWNUM <= lastday) t2 17 WHERE LAST_DAY(TO_DATE(t1.month_id, 'yyyymm')) >= 18 TO_DATE(t1.month_id, 'yyyymm') + t2.rn - 1 19 ORDER BY t1.month_id, 20 t2.rn 21 ;EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT----------- ---------- ---------- ----------2011/02/01 0 0 17.85714282011/02/02 0 0 17.85714282011/02/03 0 0 17.85714282011/02/04 0 0 17.85714282011/02/05 0 0 17.85714282011/02/06 0 0 17.85714282011/02/07 0 0 17.85714282011/02/08 0 0 17.85714282011/02/09 0 0 17.85714282011/02/10 0 0 17.85714282011/02/11 0 0 17.85714282011/02/12 0 0 17.85714282011/02/13 0 0 17.85714282011/02/14 0 0 17.85714282011/02/15 0 0 17.85714282011/02/16 0 0 17.85714282011/02/17 0 0 17.85714282011/02/18 0 0 17.85714282011/02/19 0 0 17.85714282011/02/20 0 0 17.8571428EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT----------- ---------- ---------- ----------2011/02/21 0 0 17.85714282011/02/22 0 0 17.85714282011/02/23 0 0 17.85714282011/02/24 0 0 17.85714282011/02/25 0 0 17.85714282011/02/26 0 0 17.85714282011/02/27 0 0 17.85714282011/02/28 0 0 17.85714282011/11/01 2 2 202011/11/02 2 2 202011/11/03 2 2 202011/11/04 2 2 202011/11/05 2 2 202011/11/06 2 2 202011/11/07 2 2 202011/11/08 2 2 202011/11/09 2 2 202011/11/10 2 2 202011/11/11 2 2 202011/11/12 2 2 202011/11/13 2 2 20EVERY_DAY COMPANY_ID TYPE_ID AVG_AMOUNT----------- ---------- ---------- ----------2011/11/14 2 2 202011/11/15 2 2 202011/11/16 2 2 202011/11/17 2 2 202011/11/18 2 2 202011/11/19 2 2 202011/11/20 2 2 202011/11/21 2 2 202011/11/22 2 2 202011/11/23 2 2 202011/11/24 2 2 202011/11/25 2 2 202011/11/26 2 2 202011/11/27 2 2 202011/11/28 2 2 202011/11/29 2 2 202011/11/30 2 2 2058 rows selectedSQL> WITH tb AS ( 2 SELECT 'KC0003' min_card_id,'KC0012' max_card_id,'ww' source_dept FROM DUAL UNION ALL 3 SELECT 'KB0006' min_card_id,'KB0010' max_card_id,'aa' source_dept FROM DUAL 4 ) 5 SELECT SUBSTR(t1.min_card_id,1,2) || TO_CHAR(SUBSTR(t1.min_card_id,3) + rn - 1,'fm0999') card_id, 6 t1.source_dept 7 FROM tb t1, 8 (SELECT ROWNUM rn 9 FROM (SELECT MAX(SUBSTR(max_card_id,3) - SUBSTR(min_card_id,3)) + 1 loop_num 10 FROM tb) 11 CONNECT BY ROWNUM <= loop_num) t2 12 WHERE SUBSTR(max_card_id,3) >= SUBSTR(t1.min_card_id,3) + rn - 1 13 ORDER BY t1.source_dept,t2.rn 14 ;CARD_ID SOURCE_DEPT----------- -----------KB0006 aaKB0007 aaKB0008 aaKB0009 aaKB0010 aaKC0003 wwKC0004 wwKC0005 wwKC0006 wwKC0007 wwKC0008 wwKC0009 wwKC0010 wwKC0011 wwKC0012 ww15 rows selected