SQL语句查询，同表group by 提取有两个相同字段的数据
表结构tb_submit（有主键，keyid）
custidsubmitdate
1err2013/5/1 11:00:01
1ok2013/5/1 11:00:03
1err2013/5/1 11:00:02
2err2013/5/1 12:00:01
2ok2013/5/1 11:00:03
2err2013/5/1 11:00:02
3err2013/5/1 12:00:01
3ok2013/5/1 11:00:03
3ok2013/5/1 11:00:02
现在想要提取tb_submit其中的数据。
同个custid下 有两条submit为err状态，且两个err状态的时间相差10秒内，跪求代码！
比如上面的例子，就会提取出
custidsubmitdate
1err2013/5/1 11:00:01
1err2013/5/1 11:00:02
2err2013/5/1 12:00:01
2err2013/5/1 11:00:02
谢谢各位大牛！！！。
[解决办法]
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';
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
