oracle中止号的取得
oracle中断号的取得
1.根据号码求出号段
结果：
miss_no
3
5
7
9
10
11
12
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
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