34,838
社区成员




if object_id('tb') is not null drop table tb
create table tb(F1 varchar(10),id int) ----测试数据
insert tb select 'C',5
insert tb select 'A',1
insert tb select 'A',2
insert tb select 'B',1
insert tb select 'B',4
insert tb select 'B',7
insert tb select 'C',7
insert tb select 'C',5
1.select * from tb a where exists(select * from tb where F1=a.F1 and id<a.id)
2.select * from tb a where id in (select id from tb where F1=a.F1 and id<a.id)
3.select distinct * from tb a where not exists(select 1 from tb where F1=a.F1 and id<a.id)
先理解他们的用法...
查询计划,dbms帮做的不用管他...
A 2
B 4
B 7
C 7
1.select * from tb a where exists(select * from tb where F1=a.F1 and id<a.id)
--先执行select * from tb where F1=a.F1 and id<a.id然后执行where exists最后是select * from tb a