SQL Server and DISTINCT

Question

我已经花了几个小时在谷歌上搜索解决问题的好方法，但我一无所获。

我有两个表:table1和table2，我想找到table1中没有的所有唯一的电子邮件。

第一个查询返回一个int值1420的结果，第二个查询返回一个总计数为1431的行集。

这怎么可能?我猜这和我选择名字和状态有关吧?

select 
    Count(DISTINCT(Email)) 
from
    table1 t1 
where 
    t1.siteId = 4 
    and t1.Email not in (select Email from table2 t2 where t2.SiteId = 4)
go
WITH MyPage AS
(
   SELECT DISTINCT(t1.Email), 
       t1.Name, 
       t1.[Status],
       ROW_NUMBER() OVER (partition by t1.Email ORDER BY t1.Name) AS Apperences,
       ROW_NUMBER() OVER (ORDER BY t1.Name) AS RowNumber
   FROM 
       table1 t1 
   WHERE 
       t1.siteId = 4 
       AND t1.Email NOT IN (SELECT Email FROM table2 t2 WHERE t2.SiteId = 4)
)
SELECT 
    Email, Name, [Status]
FROM 
    MyPage
WHERE 
    RowNumber => 0 and RowNumber <= 50000

我知道我可以在最后一次选择中添加"distinct"，但这会破坏我想要的分页效果。

Answer 1

试试这个:

WITH MyPage 
AS
  (SELECT 
      t1.Email, 
      t1.Name, 
      t1.[Status],
      ROW_NUMBER() OVER (partition by t1.Email ORDER BY t1.Name) AS Apperences,
      ROW_NUMBER() OVER (ORDER BY t1.Name) AS RowNumber
   FROM table1 t1 
   WHERE t1.siteId = 4 
   and t1.Email not in (SELECT Email FROM table2 t2 where t2.SiteId=4)
  )
SELECT Email, Name, [Status]
FROM MyPage
WHERE RowNumber => 0 and RowNumber <= 50000
AND Apperences = 1 // this will give you the DISTINCT email

Answer 2

要获得所有不同的电子邮件的列表和仅应用于它的一个名称，首先创建一个具有示例名称的所有不同邮件的列表(我使用按字母顺序排列的最后一个名称)，然后使用此表作为基础添加更多信息。但是，结果，特别是行号将是无用的。

    WITH MyPage AS
      (SELECT t1.Email, 
        t1.Name, 
        t1.[Status],
        ROW_NUMBER() OVER (partition by t1.Email ORDER BY t1.Name) AS Apperences,
        ROW_NUMBER() OVER (ORDER BY t1.Name) AS RowNumber
        FROM 
          (select x.Email, max(Name) as name 
             from table1 t1 
            where t1.siteId = 4 
              and t1.Email not in (
              SELECT Email FROM table2 t2 where t2.SiteId=4
              )
            group by x.Email
          ) t1
    SELECT Email, Name, [Status]
    FROM MyPage
    WHERE RowNumber => 0 and RowNumber <= 50000