sql - 伪SQL？名字和姓氏重复但城市不同的所有记录？

Question

我需要找到 FIRSTNAME 重复且 LASTNAME 重复但城市与名称重复的记录不同的所有记录。

所以我的数据是这样的:

FirstName    LastName    CustomerFileLocation          City
----------------------------------------------------------------------
   Joe        Smith         c:\file1\File1.txt         Dallas
   Joe        Jones         c:\File2\File1.txt         New York
   Joe        Smith         c:\File3\File1.txt         New Mexico City
   Harry      Smith         c:\File4\File1.txt         Boca Raton
   Joe        Smith         c:\File3\File1.txt         Dallas
   Michael    Smith         c:\File1\File1.txt         Dallas

我希望查询返回

   Joe        Smith         c:\file1\File1.txt         Dallas
   Joe        Smith         c:\File3\File1.txt         New Mexico City

我编写了以下代码来查找匹配的 FirstName 和 LastName。但我不确定如何说“和城市不匹配”

SELECT        
   dbo.TblFileCache.FirstName, dbo.TblFileCache.LastName, 
   dbo.TblFileCache.ClaimFilePath, dbo.TblFileCache.Skip
FROM
   dbo.TblFileCache 
INNER JOIN
   (SELECT        
       FirstName, LastName, COUNT(*) AS CountOf
    FROM 
       dbo.TblFileCache AS tblFileCache_1
    GROUP BY 
       FirstName, LastName 
    HAVING         
       (COUNT(*) > 1)) AS dt ON dbo.TblFileCache.FirstName = dt.FirstName 
                             AND dbo.TblFileCache.LastName = dt.LastName
WHERE        
    (dbo.TblFileCache.Skip = 0)
ORDER BY 
    dbo.TblFileCache.FirstName, dbo.TblFileCache.LastName

Answer 1

要获取原始数据中一组用户名包含多个城市的所有行，您可以使用窗口函数:

select t.*
from (select t.*,
             min(city) over (partition by FirstName, LastName) as mincity,
             max(city) over (partition by FirstName, LastName) as maxcity
      from dbo.TblFileCache t
     ) t
where mincity <> maxcity;

如果您希望每个城市一行，您可以在此基础上进行聚合:

select FirstName, LastName, min(CustomerFileLocation) as CustomerFileLocation, city
from (select t.*,
             min(city) over (partition by FirstName, LastName) as mincity,
             max(city) over (partition by FirstName, LastName) as maxcity
      from dbo.TblFileCache t
     ) t
where mincity <> maxcity
group by FirstName, LastName, City;