/*
--TB1表数据示例:
CusID IDtimes Atime Btime Ctime Dtype
A-A 1 0 0 23 3
A-A 2 30 0 0 1
A-A -1 10 1 120 2
B-B 1 0 0 11 3
B-B -1 50 0 0 1
--...
*/DESC TB1;
/*
CusID NOT NULL VARCHAR2(25)
IDtimes NUMBER(4)
Atime/Btime/Ctime DATE
Dtype NUMBER(4)
*/--求Dtype=2且(Btime+Ctime)>0记录同CusID的、满足的Dtype=1且IDtimes=2的记录行!
--求教,谢谢方家~
--------<2010.12.21 afternoon>
where t.Dtype=1 and t.IDtimes=2 and
exists(select null from TB1 t1
where t.CusID=t1.CusID and t1.Dtype=2 and (t1.Btime+t1.Ctime)>0)
--其实这句话没怎么明白:
--求Dtype=2且(Btime+Ctime)>0记录同CusID的、满足的Dtype=1且IDtimes=2的记录行!
select count(*) cnt from TB1 a
where a.Dtype=2 and (a.Btime+a.Ctime)>0
and exists(select 1 from TB1 b where a.CusID=b.CusID and b.Dtype=1 and b.IDtimes=2 )
--意思有点模糊
select CusID,IDtimes,Atime,Btime ,Ctime ,Dtype
from tb a
where Dtype=2 and (Btime+Ctime)>0
and exists(select 1 from tb b where a.CusID=b.CusID and b.Dtype=1 and b.IDtimes=2)
谢谢各位及时响应,呵呵~
^^……可惜,TB1十分大~
没有index,太慢了,
in & exists如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in:例如:表A(小表),表B(大表)
1:
select * from A where cc in (select cc from B)
效率低,用到了A表上cc列的索引;
select * from A where exists(select cc from B where cc=A.cc)
效率高,用到了B表上cc列的索引。
相反的
2:
select * from B where cc in (select cc from A)
效率高,用到了B表上cc列的索引;
select * from B where exists(select cc from A where cc=B.cc)
效率低,用到了A表上cc列的索引。