再求上这个SQL如何写请给具体SQL啊多谢_Oracle技术

再求高手指点下这个SQL怎么写请给具体SQL啊谢谢
col1 col2 col3
2012-01-01 1000 0001
2012-01-01 2222 0004
2012-01-01 3000 0001
2012-01-02 1000 0002
2012-01-02 2222 0004
2012-01-02 3000 0003
2012-01-03 1000 0002
2012-01-03 2222 0004
2012-01-03 3000 0004
2012-01-04 2222 0004
2012-01-04 3000 0003
2012-01-05 2222 0004
2012-01-05 3000 0003
其中col1是日期 col3是随便的数据 col1,col2,col3为联合主键
现在要查询col2在一个时间段内连续出现的数据如上的数据查询1到5号数据则结果为
col2
2222
3000

------解决方案--------------------

探讨
引用:
用分析函数

给个具体的例子嘛

------解决方案--------------------

SQL code

[SYS@myoracle] SQL>with t1 as(  2  select date'2012-01-01' col1, 1000 col2, '0001' col3 from dual union all  3  select date'2012-01-01' col1, 2222 col2, '0004' col3 from dual union all  4  select date'2012-01-01' col1, 3000 col2, '0001' col3 from dual union all  5  select date'2012-01-02' col1, 1000 col2, '0002' col3 from dual union all  6  select date'2012-01-02' col1, 2222 col2, '0004' col3 from dual union all  7  select date'2012-01-02' col1, 3000 col2, '0003' col3 from dual union all  8  select date'2012-01-03' col1, 1000 col2, '0002' col3 from dual union all  9  select date'2012-01-03' col1, 2222 col2, '0004' col3 from dual union all 10  select date'2012-01-03' col1, 3000 col2, '0004' col3 from dual union all 11  select date'2012-01-04' col1, 2222 col2, '0004' col3 from dual union all 12  select date'2012-01-04' col1, 3000 col2, '0003' col3 from dual union all 13  select date'2012-01-05' col1, 2222 col2, '0004' col3 from dual union all 14  select date'2012-01-05' col1, 3000 col2, '0003' col3 from dual 15  )select col2 16     from (select row_number()over(partition by col2 order by col1) row_, 17                  col1,col2,col3 18             from t1 19            where col1 between date'2012-01-01' and date'2012-01-05'               --这个where条件是你需要根据你自己的需要改动的地方 20           ) 21    group by col2,col1-row_ 22    having count(1) = date'2012-01-05'-date'2012-01-01' + 1                        --这个条件也是你需要根据你自己的需要改动的地方、 23    order by col2;      COL2----------      2222      3000[SYS@myoracle] SQL>
------解决方案--------------------
SQL codewith t as(    select date'2012-01-01' col1, 1000 col2, '0001' col3 from dual union all    select date'2012-01-01' col1, 2222 col2, '0004' col3 from dual union all    select date'2012-01-01' col1, 3000 col2, '0001' col3 from dual union all    select date'2012-01-02' col1, 1000 col2, '0002' col3 from dual union all    select date'2012-01-02' col1, 2222 col2, '0004' col3 from dual union all    select date'2012-01-02' col1, 3000 col2, '0003' col3 from dual union all    select date'2012-01-03' col1, 1000 col2, '0002' col3 from dual union all    select date'2012-01-03' col1, 2222 col2, '0004' col3 from dual union all   select date'2012-01-03' col1, 3000 col2, '0004' col3 from dual union all   select date'2012-01-04' col1, 2222 col2, '0004' col3 from dual union all   select date'2012-01-04' col1, 3000 col2, '0003' col3 from dual union all   select date'2012-01-05' col1, 2222 col2, '0004' col3 from dual union all   select date'2012-01-05' col1, 3000 col2, '0003' col3 from dual   )   SELECT col2 FROM    (      SELECT (SELECT Count(DISTINCT col1) FROM t WHERE col1 between date'2012-01-01' and date'2012-01-05' )ncol1,      Count(col2)ncol2,      col2       FROM       (        SELECT DISTINCT col1,col2 FROM t WHERE col1 between date'2012-01-01' and date'2012-01-05'      )GROUP BY col2   )WHERE ncol1=ncol2
------解决方案--------------------
其实就是这样！
with t as(