我有此SQL语句（已修改，因为实际查询量很大）：
select tblInfo.IDNum, tblAddress.PrimaryAddress
from tblInfo
join tblAddress
on tblInfo.Agent = tblAddress.Agent
where (some stuff)
我得到一张看起来大致像这样的表：
|| IDNum || PrimaryAddress ||
-----------------------------
|| 01234 || 1 ||
|| 23456 || 1 ||
|| abcde || 0 ||
|| abcde || 1 ||
|| zyxwv || 0 ||
我需要一种方法来返回所有具有PrimaryAddress为1的记录，以及所有具有PrimaryAddress为0并且没有IDNum已经返回PrimaryAddress为1的IDNum的记录。即在上面的示例中，（abcde || 0）应该被排除，因为（abcde || 1）存在。
使用 NOT EXISTS
SELECT tblInfo.IDNum, tblAddress.PrimaryAddress
FROM tblInfo
INNER JOIN tblAddress
ON tblInfo.Agent = tblAddress.Agent
WHERE tblAddress.PrimaryAddress = 1
OR ( tblAddress.PrimaryAddress = 0 AND NOT EXISTS
(
SELECT 1 FROM tblInfo t2 INNER JOIN tblAddress a2 ON t2.Agent = a2.Agent
WHERE t2.IDNum = tblInfo.IDNum AND a2.PrimaryAddress = 1
)
)
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句