使用 NULL 检查重复项的 SQL 不会不返回

Question

我正在使用 2 个单独的查询来检查重复项，两个查询在执行时应该给出相同的答案，但它没有。 下面是我的数据库表数据的示例：

id   Name   Age  Group SeatNo
------------------------------------------
1    Alpha  11    A    NULL
2    Bravo  12    A    1
3    Alpha  11    B    NULL

这是我的第一个查询，它显示有重复项

SELECT count(*)
FROM Test AS ta
JOIN Test AS tb ON ta.name=tb.name AND ta.age=tb.age
WHERE ta.GroupName='A'
AND tb.GroupName='B'

这是我的第二个查询，显示零重复项

SELECT count(*)
FROM Test AS ta
JOIN Test AS tb ON ta.name=tb.name AND ta.age=tb.age AND ta.SeatNo=tb.SeatNo
WHERE ta.GroupName='A'
AND tb.GroupName='B

在查看了查询和表中的数据后，似乎 seatNo 中的 NULL 影响了第二个查询并导致它返回 0 个重复项。有没有办法搜索重复项，即使它是空的？

Answer 1

你已经学会了NULL <> NULL. 你能做些什么呢？

好吧，ANSI SQL有一个用于此目的的比较器IS DISTINCT FROM。 所以，你可以写：

SELECT count(*)
FROM Test AS ta JOIN
     Test AS tb
     ON ta.name = tb.name AND ta.age = tb.age AND
        NOT ta.SeatNo IS DISTINCT FROM tb.SeatNo
WHERE ta.GroupName = 'A' AND
      tb.GroupName = 'B';

但是，大多数数据库不支持此功能。 更一般的形式是：

SELECT count(*)
FROM Test AS ta JOIN
     Test AS tb
     ON ta.name = tb.name AND ta.age = tb.age AND
        (ta.SeatNo = tb.SeatNo OR (ta.SeatNo IS NULL AND tb.SeatNo IS NULL) )
WHERE ta.GroupName = 'A' AND
      tb.GroupName = 'B';

另一种方法使用UNION ALL和聚合。 以下内容获取重复项：

select name, age, SeatNo
from ((select name, age, SeatNo, 'a' as which
       from test
       where GroupName = 'A'
      ) union all
      (select name, age, SeatNo, 'B' as which
       from test
       where GroupName = 'B'
      )
     ) ab
group by name, age, seatno
having count(distinct which) = 2;

然后，可以将其用作子查询来获取计数：

select count(*)
from (select name, age, SeatNo
      from ((select name, age, SeatNo, 'a' as which
             from test
             where GroupName = 'A'
            ) union all
            (select name, age, SeatNo, 'B' as which
             from test
             where GroupName = 'B'
            )
           ) ab
      group by name, age, seatno
      having count(distinct which) = 2  
     ) ab;

Answer 2

对于第二个查询，您可以在 JOIN 中添加另一个筛选条件

SELECT count(*)
FROM Test AS ta
JOIN Test AS tb ON ta.name=tb.name AND 
                   ta.age=tb.age AND 
                   ( ta.SeatNo=tb.SeatNo OR (ta.SeatNo IS NULL AND tb.SeatNo IS NULL)
WHERE ta.GroupName='A'
AND tb.GroupName='B

<</div> div class="one_answers">第二个

查询应检查 NULL。尝试

SELECT count(*)
FROM Test AS ta
JOIN Test AS tb ON ta.name=tb.name AND ta.age=tb.age AND ISNULL(ta.SeatNo,'')=ISNULL(tb.SeatNo,'')
WHERE ta.GroupName='A'
AND tb.GroupName='B