oracle中断号的取得

sylwk1r0ry

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   0001
1   0002
1   0003
1   0005
1   0007
2   0011
2   0012
2   0023
2   0035
1   0008
2   0036
-- 期望结果：
TID MINNUM MAXNUM
--- ------ ------
1   0001   0003
1   0005   0005
1   0007   0008
2   0011   0012
2   0023   0023
2   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
) n
GROUP BY n.tid,n.group_num
ORDER 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 >= 0
CONNECT 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.8571428
2011/02/02           0          0 17.8571428
2011/02/03           0          0 17.8571428
2011/02/04           0          0 17.8571428
2011/02/05           0          0 17.8571428
2011/02/06           0          0 17.8571428
2011/02/07           0          0 17.8571428
2011/02/08           0          0 17.8571428
2011/02/09           0          0 17.8571428
2011/02/10           0          0 17.8571428
2011/02/11           0          0 17.8571428
2011/02/12           0          0 17.8571428
2011/02/13           0          0 17.8571428
2011/02/14           0          0 17.8571428
2011/02/15           0          0 17.8571428
2011/02/16           0          0 17.8571428
2011/02/17           0          0 17.8571428
2011/02/18           0          0 17.8571428
2011/02/19           0          0 17.8571428
2011/02/20           0          0 17.8571428
EVERY_DAY   COMPANY_ID    TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/02/21           0          0 17.8571428
2011/02/22           0          0 17.8571428
2011/02/23           0          0 17.8571428
2011/02/24           0          0 17.8571428
2011/02/25           0          0 17.8571428
2011/02/26           0          0 17.8571428
2011/02/27           0          0 17.8571428
2011/02/28           0          0 17.8571428
2011/11/01           2          2         20
2011/11/02           2          2         20
2011/11/03           2          2         20
2011/11/04           2          2         20
2011/11/05           2          2         20
2011/11/06           2          2         20
2011/11/07           2          2         20
2011/11/08           2          2         20
2011/11/09           2          2         20
2011/11/10           2          2         20
2011/11/11           2          2         20
2011/11/12           2          2         20
2011/11/13           2          2         20
EVERY_DAY   COMPANY_ID    TYPE_ID AVG_AMOUNT
----------- ---------- ---------- ----------
2011/11/14           2          2         20
2011/11/15           2          2         20
2011/11/16           2          2         20
2011/11/17           2          2         20
2011/11/18           2          2         20
2011/11/19           2          2         20
2011/11/20           2          2         20
2011/11/21           2          2         20
2011/11/22           2          2         20
2011/11/23           2          2         20
2011/11/24           2          2         20
2011/11/25           2          2         20
2011/11/26           2          2         20
2011/11/27           2          2         20
2011/11/28           2          2         20
2011/11/29           2          2         20
2011/11/30           2          2         20
58 rows selected

SQL> 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      aa
KB0007      aa
KB0008      aa
KB0009      aa
KB0010      aa
KC0003      ww
KC0004      ww
KC0005      ww
KC0006      ww
KC0007      ww
KC0008      ww
KC0009      ww
KC0010      ww
KC0011      ww
KC0012      ww
15 rows selected