日期:2014-05-16 浏览次数:21255 次
[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>
------解决方案--------------------
with 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(