-- 
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

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;

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