SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.TID=TID)
[code=SQ]在 两个表的 tid 都建立索引例: create index ta_index on A(tid) create index tb_index on B(tid) select count(*) from A ,B where a.tid = b.tid[/code]
select top 1 * from a , b where a.tid = b.tidselect top 1 a.* from a where exists(select 1 from b where b.tid = a.tid)
SELECT 1 FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.TID=TID)
[code=SQ] SELECT COUNT(*) FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.TID=TID) [/code]
[code=SQ]select * from a where exists(select 1 from b where b.tid = a.tid)[/code]
[code=SQ]select * from a where exists(select 1 from b where b.tid = a.tid)[/code]
不好意思,事实上需要可能要更复杂点,用上边的方法不知道怎么写事实是两个表并不一定是一个单独的表,可能是通过inner join 连接起来的,就是说我的具体需求是:select tid from A inner join C on A.field1=C.fild2 where 筛选条件select tid from B inner join D on B.field1=D.fild2 where 筛选条件然后这两个查询得到的结果可能有重复的,所以我想判断有没有重复的,但用exists或者是in的查询速度过慢(索引并不起作用),所以希望有一个较为快速的方法
[code=SQ]select count(*) from (select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t left join (select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e on t.tid = e.tid where e.tie is not null[/code]
select count(*) as rn,t.tid as tid from (select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t left join (select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e on t.tid = e.tid where e.tie is not null rn是看有没重复的,如果有 tid 就是重复的值。
select count(*) as rn,t.tid as tid from (select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t, (select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e where t.tid = e.tid
create index ta_index on A(tid)
create index tb_index on B(tid) select count(*) from A ,B where a.tid = b.tid[/code]
SELECT 1 FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.TID=TID)
SELECT COUNT(*) FROM A WHERE EXISTS(SELECT 1 FROM B WHERE A.TID=TID)
[/code]
where exists(select 1 from b where b.tid = a.tid)[/code]
from
(select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t
left join
(select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e
on t.tid = e.tid
where e.tie is not null[/code]
from
(select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t
left join
(select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e
on t.tid = e.tid
where e.tie is not null rn是看有没重复的,如果有 tid 就是重复的值。
from
(select a.tid from A inner join C on A.field1=C.fild2 where 筛选条件)t,
(select b.tid from B inner join D on B.field1=D.fild2 where 筛选条件)e
where t.tid = e.tid