100分跪求各位大虾

[Kelvin峰]

查询的结果是
select U.MailAddr,UA.*
from U left join UA
on U.MailAddr = UA.MailAddr

MailAddr MailAddr UATID UAGID
jlk jlk 2 1
jlk jlk 22 1
jlk jlk 34 1
jlk jlk 11 1
jlk jlk 37 1
jlk jlk 43 1
fwx fwx 2 1
fwx fwx 22 1
fwx fwx 34 1
fwx fwx 11 1
fwx fwx 37 1
fwx fwx 43 1
aaaa aaaa 2 1
aaaa aaaa 22 1
aaaa aaaa 34 1
aaaa aaaa 11 1
aaaa aaaa 37 1
aaaa aaaa 43 1
lulu NULL NULL NULL
aaaa aaaa 49 33
如测试数据，UAGID=1该组里面有MailAddr=aaaa的数据，而组UAGID=33里面也有aaaa的数据、如果
select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 or UA.UAGID is null
查询结果将得到UAGID=33组里面的aaaa的值，显然这不是我想求的
我现在只想将
select *
from U left join UA
on U.MailAddr = UA.MailAddr and UA.UAGID = 1
where UA.MailAddr is null
里面的UA.UAGID=1这样的条件放在where之后(U表内没有UAGID，两表关联之靠MailAddr)
麻烦各位了

[CathySun118]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 and UA.MailAddr is null
这样不行吗？

[Kelvin峰]

不行，这样查出来的结果是空

[paoluo]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 or UA.UAGID is null
查询结果将得到UAGID=33组里面的aaaa的值，显然这不是我想求的

---------------

這個UAGID=33的紀錄怎麼會查詢出來？

[w75251455]

--UAGID=1该组里面有MailAddr=aaaa的数据
select U.MailAddr,UA.*
from U left join UA
on U.MailAddr = UA.MailAddr
where uagid='1' and u.mailaddr='aaaa'
-??????????

[Kelvin峰]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 or UA.UAGID is null
查询结果将得到UAGID=33组里面的aaaa的值，显然这不是我想求的

---------------

這個UAGID=33的紀錄怎麼會查詢出來？
不好意思，这个写错了

[Kelvin峰]

我现在有4条数据（4个MailAddr），分别为lulu,fwx,aaaa,jlk,在UAGID=1的组里面有三条数据，jlk,fwx,aaaa,

select *
from U left join UA
on U.MailAddr = UA.MailAddr and UA.UAGID = 1
where UA.MailAddr is null
这样查结果是正确的，MailAddr是主键
select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and (UA.UAGID = 1 or UA.UAGID is null)
这样查的话在UA.UAGID=1里面结果是正确的，但如果UA.UAGID = 33呢
select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and (UA.UAGID = 33 or UA.UAGID is null)
查出的结果还是
lulu NULL NULL NULL
而我要的结果是除了
aaaa aaaa 49 33
之外剩下的三条数据lulu,fwx,jlk(就是在UAGID=33这个组里查询这4条数据的剩下三条)

[w75251455]

where UAGID <>33

[w75251455]

where UA.MailAddr is null and (UA.UAGID = 33 or UA.UAGID is null)

UA.MailAddr是空的同时(UA.UAGID为33 或 UA.UAGID 是空)
(UA.UAGID为33 或 UA.UAGID 是空)
lulu NULL NULL NULL
aaaa aaaa 49 33
UA.MailAddr是空
lulu NULL NULL NULL

当然是
lulu NULL NULL NULL
......

[Kelvin峰]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 or UA.UAGID is null
查询结果将得到UAGID=33组里面的aaaa的值，显然这不是我想求的

---------------

這個UAGID=33的紀錄怎麼會查詢出來？
不好意思，这个写错了
应该是
select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID <> 1 or UA.UAGID is null

[believe209]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and (UA.UAGID = 1 or UA.UAGID is null)

[paoluo]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and (UA.UAGID = 33 or UA.UAGID is null)
查出的结果还是
lulu NULL NULL NULL

----------
不對吧，這條語句怎麼會沒有查詢出UA.UAGID = 33的數據?

[w75251455]

UA.UAGID <> 1
lulu NULL NULL NULL
aaaa aaaa 49 33
or
UA.UAGID is null
lulu NULL NULL NULL

:
lulu NULL NULL NULL
aaaa aaaa 49 33

[w75251455]

唉~~..怎么还在玩where啊...join都会了....

[scckobe]

select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and UA.UAGID = 1
这样不会有问题吧。