基本用法~~~~~~~~~~~~~~~~~~~~~~

SELECT * FROM tree-- where 子句 , 若有,只是过滤最终结果的作用START WITH father = '爷爷'    -- 从 father 为 '爷爷' 的 那一条记录开始-- 若有 nocyle 关键字, 则不走环, 仅列出所有映射CONNECT BY [NOCYCLE] PRIOR son = father; -- 你的 son 为 他人的 father, 搜索他人,即往下找

 

实际例子~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

问题：

数据库里有字段day_number，msisdn。如何写月度连续3天有记录的手机号？表结构如下：

BILL_MONTH           DAY_NUMBER MSISDN

-------------------- ---------- --------------------

200803                        1 1380000000

200803                        3 1380000000

200803                        2 1380000000

200803                        2 1380100000

200803                        4 1380400000

200803                        5 1380400000

表中3月份连续3天有记录的纪录就是1380000000。请问如何写这样的sql？

 

select distinct  msisdn  from test  a    where  bill_month='200803'    and exists    (       select msisdn from  test      where  bill_month='200803' and msisdn=a.msisdn      start with day_number=a.day_number      connect by  prior day_number=day_number-1 and prior msisdn= msisdn      group by msisdn      having count(*)>=3    );