sql - 带有自连接的 MS Access SQL 产生的结果比原始表少，查询中不存在的记录集代表什么？

Question

我的原始表（File05292019）有 22,904 条记录。我对如下所示的 3 个字段执行自联接，结果为 22,886。为什么会这样？缺失的记录代表什么？

SELECT File05292019.LastName, File05292019.FirstName, File05292019.SubscriberSocialSecurityNumber
FROM File05292019 
INNER JOIN File05292019 AS File05292019_1 
    ON (File05292019.SubscriberSocialSecurityNumber = File05292019_1.SubscriberSocialSecurityNumber) 
    AND (File05292019.LastName = File05292019_1.LastName) 
    AND (File05292019.FirstName = File05292019_1.FirstName)
GROUP BY File05292019.LastName, File05292019.FirstName, File05292019.SubscriberSocialSecurityNumber;

标签： sqlms-access

Answer 1

group by 的存在表明这意味着您有一些具有相同值的行

你可以试试uisng

  SELECT File05292019.LastName
    , File05292019.FirstName
    , File05292019.SubscriberSocialSecurityNumber
    count(*)
  FROM File05292019 
  GROUP BY File05292019.LastName
    , File05292019.FirstName
    , File05292019.SubscriberSocialSecurityNumber
  HAVING count(*) > 1 

查找这些行