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
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
group by col2 having count(col2) >1;
as
(
select '北京' cityid, 1 as statusid,'AAA' as machineID from dual
union
select '北京' , 0 ,'BBB' from dual
union
select '北京' , 0 ,'CCC' from dual
union
select '广州' , 1 ,'DDD' from dual
union
select '上海' , 0 ,'EEE' from dual
)
select mar.*
,decode(a,'0','0',decode(b,'0','0',100*ROUND(b/a,4)||'%'))as c
from
(
select distinct cityid
,sum(case when 1=1 then 1 else 0 end) over(partition by cityid) a
,sum(case when 1=1 and statusid=1 then 1 else 0 end) over(partition by cityid) b
from machineTable
) mar
[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>
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
count(1) = date'2012-01-05'-date'2012-01-01' + 1
控制连续出现的次数即可。
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 t
WHERE col1 >=DATE'2012-01-01'AND col1<DATE'2012-01-06'
HAVING COUNT(col2)>=5
GROUP BY col2
(select '2012-01-01' col1,1000 col2,'0001' col3 from dual union all
select '2012-01-01' ,2222 ,'0004' from dual union all
select '2012-01-01' ,3000 ,'0001' from dual union all
select '2012-01-02' ,1000 ,'0002' from dual union all
select '2012-01-02' ,2222 ,'0004' from dual union all
select '2012-01-02' ,3000 ,'0003' from dual union all
select '2012-01-03' ,1000 ,'0002' from dual union all
select '2012-01-03' ,2222 ,'0004' from dual union all
select '2012-01-03' ,3000 ,'0004' from dual union all
select '2012-01-04' ,2222 ,'0004' from dual union all
select '2012-01-04' ,3000 ,'0003' from dual union all
select '2012-01-05' ,2222 ,'0004' from dual union all
select '2012-01-05' ,3000 ,'0003' from dual)
select col2 from
(select col2 from tb,
(select to_char(to_date('2012-01-01','YYYY-MM-DD')+ rownum-1,'YYYY-MM-DD') col from dual connect by rownum<=5)tb2
where tb.col1=tb2.col)
group by col2
having count(*)>=5