大家帮我看看这个sql怎么写

Question

大家帮忙想想这个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号连续一天）

Answer 1

楼主，好像此贴之前发过吧，不是已经解决了么？select ammeter_id , max(last_time),count(1)
from 
（select id, ammeter_id, time,
row_number()over(partition by ammeter_id,order by time) rn
from table1 ）  
group by ammeter_id,time-rn
having count(1)>1;

Answer 2

SQL> select * from test;
 
                   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 rows selected
 
SQL> 
SQL> WITH t1 AS (SELECT * FROM test WHERE TIME<= to_date('2010-7-22','yyyy-mm-dd'))
  2  SELECT ammeter_id,TIME,lev FROM
  3  (SELECT ammeter_id,TIME,LEVEL lev,connect_by_isleaf leaf FROM t1 a
  4   START WITH(a.ammeter_id,a.time)IN (SELECT ammeter_id,MAX(TIME) FROM t1 b GROUP BY ammeter_id)
  5  CONNECT BY NOCYCLE PRIOR a.time-1=a.time AND PRIOR a.ammeter_id=a.ammeter_id)
  6  WHERE leaf=1 ;
 
           AMMETER_ID TIME               LEV
--------------------- ----------- ----------
                    1 2010-7-20            3
                    2 2010-7-22            1
 
SQL> 

Answer 3

SQL> WITH t1 AS (
  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> 

Answer 4

SQL> WITH t1 AS (SELECT * FROM test WHERE TIME<= to_date('2010-7-22','yyyy-mm-dd'))
  2  SELECT ammeter_id,TIME,lev FROM
  3  (SELECT ammeter_id,TIME,LEVEL lev,connect_by_isleaf leaf FROM t1 a
  4   START WITH NOT EXISTS(SELECT 1 FROM t1 b WHERE a.ammeter_id=b.ammeter_id AND b.time>a.time)
  5  CONNECT BY NOCYCLE PRIOR a.time-1=a.time AND PRIOR a.ammeter_id=a.ammeter_id)
  6  WHERE leaf=1 ;
 
           AMMETER_ID TIME               LEV
--------------------- ----------- ----------
                    1 2010-7-20            3
                    2 2010-7-22            1
 
SQL> 

Answer 5

谢谢兄弟的帮忙，orcale 提示PRIOR 为无效的运算符，我看不懂你的sql，能在忙小弟一把么？ 谢谢

Answer 6

用这个吧，效率应该会高一些。前面你引用的那个是用递归查询的，效率不怎么好。
基本思路就是用排序的序号与故障时间相加再减1.这样，连续的数据补齐后会相等。
 WITH t1 AS (
    SELECT t.ammeter_id,t.time time,t.time+row_number()over(partition by t.ammeter_id order by t.time DESC)-1 time1
      FROM test t
     WHERE t.time<=to_date('2010-7-22','yyyy-mm-dd')
     ORDER BY t.ammeter_id,t.time DESC
     )
    SELECT ammeter_id,time1,COUNT(*) FROM t1 a
     WHERE EXISTS(SELECT 1 FROM t1 b WHERE a.ammeter_id=b.ammeter_id AND a.time1=b.time)
    GROUP BY ammeter_id,time1
    ORDER BY  ammeter_id  ;

Answer 7

SELECT B.ID,
       (MAX(B.RQ) - MIN(B.RQ) + 1) DAYS,
       MIN(B.RQ) KS,
       MAX(B.RQ) JS,
       SUM(JE)
  FROM (SELECT A.*, TO_NUMBER(TO_CHAR(RQ, 'yyyyMMdd')) - ROWNUM DAYS
          FROM (SELECT * FROM TEST ORDER BY ID, RQ) A) B
 GROUP BY B.ID, B.DAYS
 ORDER BY B.ID给个思路参考：利用行数（ROWNUM ）和同一行日期数字化之后相减的数值（相邻的日期得到这个数值是相同的）和id好来分组，

Answer 8

select ammeter_id ,max(time) 结束时间,count(1) 故障天数
from  
(select id, ammeter_id, time ,
row_number()over(partition by ammeter_id order by time) rn
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,time-rn;AMMETER_ID 结束时间      故障天数
---------- ----------- ----------
1          2010-7-22            3
2          2010-7-22            1

Answer 9

那就给你个最通用的吧。就是可能有点慢
SQL> SELECT C.AMMETER_ID, C.TIME, COUNT(*)
  2    FROM (SELECT T.AMMETER_ID, T.TIME + ROWNUM TIME1, T.TIME
  3            FROM (SELECT T.AMMETER_ID, T.TIME
  4                    FROM TEST T
  5                   WHERE TIME <= TO_DATE('2010-7-22', 'yyyy-mm-dd')
  6                   ORDER BY T.AMMETER_ID, TIME DESC) T) A,
  7         (SELECT T.AMMETER_ID, T.TIME + ROWNUM TIME1, T.TIME
  8            FROM (SELECT T.AMMETER_ID, T.TIME
  9                    FROM TEST T
 10                   WHERE TIME <= TO_DATE('2010-7-22', 'yyyy-mm-dd')
 11                   ORDER BY T.AMMETER_ID, TIME DESC) T) B,
 12         (SELECT AMMETER_ID, MAX(TIME) TIME
 13            FROM TEST
 14           WHERE TIME <= TO_DATE('2010-7-22', 'yyyy-mm-dd')
 15           GROUP BY AMMETER_ID) C
 16   WHERE C.TIME = B.TIME
 17     AND B.TIME1 = A.TIME1
 18     AND A.AMMETER_ID = B.AMMETER_ID
 19     AND A.AMMETER_ID = C.AMMETER_ID
 20   GROUP BY C.AMMETER_ID, C.TIME
 21  ;
 
           AMMETER_ID TIME          COUNT(*)
--------------------- ----------- ----------
                    1 2010-7-22            3
                    2 2010-7-22            1
 
SQL> 

Answer 10

思路是先将表中每个电表最接近2010-07-22的数据筛选出来构成子查询c，再用rownum加到time字段，构建子查询a和b。通过a，b，c关联取最终值。

Answer 11

何必那么复杂啊，关键是分组和根据指定的日期，向下递归查询，我的SQL简单多了，呵呵。

Answer 12

对了，对我的SQL补充一点：
其实只对 ammeter_id 分组就可以了;
即：
select ammeter_id ,max(time) 结束时间,count(1) 故障天数
from   
(select id, ammeter_id, time ,
row_number()over(partition by ammeter_id order by time) rn
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;红色字，为你自己指定的日期条件。

Answer 13

递归和分析函数的版本给他了。可能是版本的问题，prior他那里都报错

Answer 14

啊？？？ 递归prior不行？那我不是白搭了啊！

Answer 15

我知道了 他为什么prior会报错！
因为你的那个SQL用到了更高版本的，connect_by_isleaf 好像10g才有的吧所以用我这个还是能用：
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;

Answer 16

倒，好像connect_by_root是10g才增加的，isleaf不是吧。让楼主试试你的sql，也许支持。呵呵