1.根据号码求出号段
[align=left] sn
1
2
4
6
8
13
14[/align]
结果:
miss_no
3
5
7
9
10
11
12
--
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
[align=left] sn
1
2
4
6
8
13
14[/align]
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