sql - MSSQLSRV - 过滤掉重复行的结果

Question

我在使用 SQL Server 时遇到了一个令人沮丧的问题。我需要从包含通过 ETL 加载的文件的详细信息的表创建 View 。该表包含文件 id(唯一)、文件名、serverid(与它已加载到的服务器相关)。

文件名的前 2 个字母是国家代码，即美国、英国、英国、德国 - 每个国家/地区加载了多个文件。我想获得每个国家/地区文件 ID 最高的记录。下面的查询执行此操作，但它返回每个服务器的最高记录，因此可能有多个文件 ID - 即它将返回服务器 1 和服务器 2 上该国家/地区的最高文件 ID - 我只想要最高记录句号。

我在 MySQL 上玩过一个等效的查询，并通过注释掉最后一行 (GROUP BY t.[server_id]) 使其正常工作，这似乎工作正常，但当然是 MSSQLSRV需要将 SELECT 中的所有非聚合放在 GROUP BY 语句中。

那么，我怎样才能在 SQL Server 中获得相同的结果 - 即获得一个具有最高 file_id 的结果，而不会获得不同 server_id 的重复行？

希望我说清楚了。

SELECT MAX(t.[file_id]) AS FID
    ,LEFT(t.[full_file_name], 2) AS COUNTRYCODE
    ,t.[server_id]
FROM [tracking_files] t
WHERE t.server_id IS NOT NULL
    AND t.[server_id] = (
        SELECT TOP 1 [server_id]
        FROM [tracking_files] md
        WHERE md.[file_id] = t.file_id
        )
GROUP BY LEFT(t.[full_file_name], 2)
    ,t.[server_id]

编辑:

这是我在 MySQL 中使用的样本数据，以及我得到的结果(这是期望的结果)。

在 SQL Server 中，由于我无法注释掉最后一个 GROUP BY 子句，我们看到了例如GB 的两个 file_id(一个用于服务器 1，一个用于服务器 2)

Answer 1

如果您使用的是 SQL Server 2005 或更高版本，您可以使用 ROW_NUMBER() :

SELECT  t.File_ID,
        t.full_file_name,
        t.CountryCode,
        t.Server_ID
FROM    (   SELECT  t.[File_ID],
                    t.full_file_name,
                    CountryCode = LEFT(t.full_file_name, 2),
                    t.Server_ID,
                    RowNumber = ROW_NUMBER() OVER(PARTITION BY LEFT(t.full_file_name, 2) ORDER BY [File_ID] DESC)
            FROM    [tracking_files] t
        ) t
WHERE   t.RowNumber = 1;

如果您使用的是以前的版本，您将需要使用子查询来获取每个国家/地区代码的最大文件 ID，然后返回到您的主表:

SELECT  t.[File_ID],
        t.full_file_name,
        CountryCode = LEFT(t.full_file_name, 2),
        t.Server_ID
FROM    [tracking_files] t
        INNER JOIN
        (   SELECT  MaxFileID = MAX([File_ID])
            FROM    [tracking_files] t
            GROUP BY LEFT(t.full_file_name, 2)
        ) MaxT
            ON MaxT.MaxFileID = t.[File_ID];