SQL语句查询，同表group by 提取有两个相同字段的数据

[helloor]

表结构tb_submit（有主键，keyid）


custid submit date
1 err 2013/5/1 11:00:01
1 ok 2013/5/1 11:00:03
1 err 2013/5/1 11:00:02
2 err 2013/5/1 12:00:01
2 ok 2013/5/1 11:00:03
2 err 2013/5/1 11:00:02
3 err 2013/5/1 12:00:01
3 ok 2013/5/1 11:00:03
3 ok 2013/5/1 11:00:02


现在想要提取tb_submit其中的数据。
同个custid下 有两条submit为err状态，且两个err状态的时间相差10秒内，跪求代码！

比如上面的例子，就会提取出
custid submit date
1 err 2013/5/1 11:00:01
1 err 2013/5/1 11:00:02
2 err 2013/5/1 12:00:01
2 err 2013/5/1 11:00:02


谢谢各位大牛！！！。

[vanjayhsu]

引用 5 楼 HJ_daxian 的回复:

with t1 as
(
     select 1 keyid,1 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual union all
     select 2 keyid,1 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 3 keyid,1 custid,'err' submit,to_date('2013-05-01 11:00:04','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 4 keyid,2 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 5 keyid,2 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 6 keyid,2 custid,'err' submit,to_date('2013-05-01 11:00:02','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 7 keyid,3 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 8 keyid,3 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 9 keyid,3 custid,'ok'  submit,to_date('2013-05-01 11:00:02','yyyy-mm-dd hh24:mi:ss') tdate from dual 
)
 
select t1.* 
from t1,
  (select custid,(max(tdate)-min(tdate))*24*60*60 m_date
  from t1
  where submit = 'err' 
  group by custid) t2 
where t1.custid = t2.custid and t2.m_date > 0  and t2.m_date < 10 and t1.submit = 'err'
 
    keyid   custid  submit     tdate 
--------------------------------------------------------------
1    1    1    err    2013/5/1 11:00:01
2    3    1    err    2013/5/1 11:00:04
3    4    2    err    2013/5/1 11:00:01
4    6    2    err    2013/5/1 11:00:02

这个效率应该还可以。。。

[helloor]

我只是点了一次，怎么变成发两贴啦囧

[u010412956]

with t1 as
 (select t.custid,
         (t.day - lag(t.day) over(partition by t.custid order by day)) * 24 * 60 * 60 lday,
         count(1) over(partition by custid) cnt
    from tb_submit t
   where t.submit = 'err')
select *
  from tb_submit t
 where exists (select 1
          from t1
         where t.custid = t1.custid
           and t1.lday <= 10
           and t1.cnt = 2)
   and t.submit = 'err';

[helloor]

感谢，忘了说表有100W条数据，这个效率高吗

[u010412956]

引用 3 楼 helloor 的回复:

感谢，忘了说表有100W条数据，这个效率高吗

估计不是很高，你试下吧

[大明湖畔_帅锅]

通过计算同一组内submit为'err'的时间差 来得出需要的数据

with t1 as
(
     select 1 keyid,1 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual union all
     select 2 keyid,1 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 3 keyid,1 custid,'err' submit,to_date('2013-05-01 11:00:04','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 4 keyid,2 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 5 keyid,2 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 6 keyid,2 custid,'err' submit,to_date('2013-05-01 11:00:02','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 7 keyid,3 custid,'err' submit,to_date('2013-05-01 11:00:01','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 8 keyid,3 custid,'ok'  submit,to_date('2013-05-01 11:00:03','yyyy-mm-dd hh24:mi:ss') tdate from dual  union all
     select 9 keyid,3 custid,'ok'  submit,to_date('2013-05-01 11:00:02','yyyy-mm-dd hh24:mi:ss') tdate from dual 
)

select t1.* 
from t1,
  (select custid,(max(tdate)-min(tdate))*24*60*60 m_date
  from t1
  where submit = 'err' 
  group by custid) t2 
where t1.custid = t2.custid and t2.m_date > 0 and t1.submit = 'err'

    keyid   custid  submit     tdate 
--------------------------------------------------------------
1	1	1	err	2013/5/1 11:00:01
2	3	1	err	2013/5/1 11:00:04
3	4	2	err	2013/5/1 11:00:01
4	6	2	err	2013/5/1 11:00:02

[大明湖畔_帅锅]

掉了一个条件 最下面加个 and t2.m_date <= 10