SQL不返回预期记录

Question

这一定是我的一个简单的错误。我有一张有许可证的桌子(申请人有一张许可证)--上一季约600英镑过期，前一季900英镑。我需要生成一个唯一的申请人，在过去两个赛季有许可证的邮寄名单。

SELECT COUNT(*) FROM Backyard_Burn WHERE YEAR(Expiration_Date)= 2014

SELECT COUNT(*) FROM Backyard_Burn WHERE YEAR(Expiration_Date)= 2013

SELECT COUNT(*) FROM Backyard_Burn WHERE YEAR(Expiration_Date)= 2013
AND Applicant_Mail_ID NOT IN(
  SELECT Applicant_Mail_ID 
  FROM Backyard_Burn 
  WHERE YEAR(Expiration_Date)= 2014)

返回: 618、923和0。

为什么在923-618附近的某个地方，假设大多数是重复申请者，为什么不是一个数字？

Answer 1

NOT IN可能是危险的。这个问题可能是由于Applicant_Mail_id采用了NULL值而造成的。您可以很容易地通过以下方法修复这个问题：

SELECT COUNT(*)
FROM Backyard_Burn
WHERE YEAR(Expiration_Date) = 2013 AND
      Applicant_Mail_ID NOT IN (SELECT Applicant_Mail_ID 
                                FROM Backyard_Burn 
                                WHERE YEAR(Expiration_Date) = 2014 AND Applicant_Mail_ID IS NOT NULL
                               );

如果这些值中的任何一个是NULL，那么NOT IN只能返回FALSE或NULL --该条件永远不会允许记录通过。

因此，我认为最好使用NOT EXSTS，当其中一些值可能是NULL时，它具有您所期望的语义。

SELECT COUNT(*)
FROM Backyard_Burn bb
WHERE YEAR(Expiration_Date) = 2013 AND
      NOT EXISTS (SELECT 1 
                  FROM Backyard_Burn bb2
                  WHERE YEAR(bb2.Expiration_Date) = 2014 AND
                        bb2.Applicant_Mail_ID = bb.Applicant_Mail_ID
                 );

编辑：

顺便说一句，另一种方法是使用group by和having

select Applicant_Mail_ID
from Backyard_Burn
group by Applicant_Mail_ID
having sum(case when year(Expiration_Date) = 2013 then 1 else 0 end) > 0 and
       sum(case when year(Expiration_Date) = 2014 then 1 else 0 end) > 0;

这避免了NULL的问题，并使得添加新的条件变得很容易，比如在2012年没有任何记录的申请者。

Answer 2

1. 你需要过去两个赛季的申请者--你需要使用一个比运算符更好的人。
2. 最好是检查完整日期，而不是使用year获取年份值。
3. 要获得唯一的申请者，可以使用distinct

其结果是：

 select count(distinct Applicant_Mail_ID)
 from Backyard_Burn
 where Expiration_Date >= '20130101';