c# - 检查 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 个重复项。是否有任何解决方案可以搜索重复项，即使它是 NULL？

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;