大家帮忙想想这个sql怎么写。小弟不胜感激我有这样一个表用来记录电表的故障,
id ammeter_id time
1 1 2010-1-1
2 1 2010-7-20
3 1 2010-7-21
4 1 2010-7-22
5 2 2010-7-1
6 2 2010-7-2
7 2 2010-7-22如果我向搜索7月22号以前连续发生故障的电表,有人会每天记录有故障的电表
则返回 ammeter_id time last_time
1 2010-7-22 3 (因为20、21、22 连续三天)
2 2010-7-22 1 (因为只有22号连续一天)
id ammeter_id time
1 1 2010-1-1
2 1 2010-7-20
3 1 2010-7-21
4 1 2010-7-22
5 2 2010-7-1
6 2 2010-7-2
7 2 2010-7-22如果我向搜索7月22号以前连续发生故障的电表,有人会每天记录有故障的电表
则返回 ammeter_id time last_time
1 2010-7-22 3 (因为20、21、22 连续三天)
2 2010-7-22 1 (因为只有22号连续一天)
2 SELECT t.ammeter_id,t.time,t.time+row_number()over(partition by t.ammeter_id order by t.time DESC)-1 time1
3 FROM test t
4 WHERE t.time<=to_date('2010-7-22','yyyy-mm-dd')
5 ORDER BY t.ammeter_id,t.time DESC
6 )
7 SELECT ammeter_id,time1,COUNT(*) FROM t1
8 WHERE ( ammeter_id,time1) IN (SELECT ammeter_id,max(time1) FROM t1 GROUP BY ammeter_id)
9 GROUP BY ammeter_id,time1
10 ORDER BY ammeter_id
11 ;
AMMETER_ID TIME1 COUNT(*)
--------------------- ----------- ----------
1 2010-7-22 3
2 2010-7-22 1
SQL>
已经解决,最简单的SQL:
select ammeter_id ,max(time) 结束时间,count(1) 故障天数
from
(select id, ammeter_id, time
from test where time<= to_date('2010-7-22','yyyy-mm-dd')
)
start with time= to_date('2010-7-22','yyyy-mm-dd')
connect by PRIOR time-1=time AND PRIOR ammeter_id=ammeter_id
group by ammeter_id;红色的为你条件,即指定随便查看哪天。