create table tt(username varchar(20),sex varchar(10),age int)
GOinsert into tt values('Betty','female',35)
insert into tt values('Sam','male',30)
insert into tt values('Tim','male',29)
insert into tt values('Larry','female',25)
insert into tt values('Mary','female',28)
GOselect t.* from tt t where
not exists(select 1 from tt where t.username=username and age>30 and sex='male')
GO--得到如下结果:
username sex age
---------------------
Betty female 35
Sam male 30
Tim male 29
Larry female 25
Mary female 28
为什么直接无视我的这个条件:
and age>30 and sex='male'
GOinsert into tt values('Betty','female',35)
insert into tt values('Sam','male',30)
insert into tt values('Tim','male',29)
insert into tt values('Larry','female',25)
insert into tt values('Mary','female',28)
GOselect t.* from tt t where
not exists(select 1 from tt where t.username=username and age>30 and sex='male')
GO--得到如下结果:
username sex age
---------------------
Betty female 35
Sam male 30
Tim male 29
Larry female 25
Mary female 28
为什么直接无视我的这个条件:
and age>30 and sex='male'
--Mary female 28
在我看来应该是这两个才对啊,
not exists(select 1 from #a b where t.username=b.username or age>30 or sex='male')
結果是正確的 sex='female'--改為這樣,第1條不會顯示
create table tt(username varchar(20),sex varchar(10),age int)
GOinsert into tt values('Betty','female',35)
insert into tt values('Sam','male',30)
insert into tt values('Tim','male',29)
insert into tt values('Larry','female',25)
insert into tt values('Mary','female',28)
GOselect
*
from
tt t
where
age<30 or sex='female'
and
not exists(select 1 from tt where username=t.username)GOdrop table tt
/*username sex age
-------------------- ---------- -----------
Tim male 29
Larry female 25
Mary female 28(3 行受影响)*/
扫描t1的Betty 从t2中找与Betty相同,又满足age>30 and sex='male'这个条件的记录,由于不存在,所以t1中的Betty是结果集
再扫描t1中的'Sam',同理分析
not exists(select 1 from tt where t.username=username and age>30 )
and sex='male'
username sex age
-------------------- ---------- -----------
Sam male 30
Tim male 29(2 行受影响)
not exists(select 1 from tt where t.username=username and (age>30 or sex='male'))
GO
not exists(select 1 from tt where t.username=username and (age>30 or sex='male'))
GO/**
username sex age
-------------------- ---------- -----------
Larry female 25
Mary female 28(所影响的行数为 2 行)
**/