select *
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 and UA.MailAddr is null
这样不行吗?
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 and UA.MailAddr is null
这样不行吗?
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 U.MailAddr,UA.*
from U left join UA
on U.MailAddr = UA.MailAddr
where uagid='1' and u.mailaddr='aaaa'
-??????????
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.UAGID = 1 or UA.UAGID is null
查询结果将得到UAGID=33组里面的aaaa的值,显然这不是我想求的---------------這個UAGID=33的紀錄怎麼會查詢出來?
不好意思,这个写错了
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条数据的剩下三条)
(UA.UAGID为33 或 UA.UAGID 是空)
lulu NULL NULL NULL
aaaa aaaa 49 33
UA.MailAddr是空
lulu NULL NULL NULL 当然是
lulu NULL NULL NULL
......
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
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and (UA.UAGID = 1 or UA.UAGID is null)
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的數據?
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
from U left join UA
on U.MailAddr = UA.MailAddr
where UA.MailAddr is null and UA.UAGID = 1
这样不会有问题吧。