如何分组查询结果避免重复行？SQL多表查询优化求助

你遇到的重复行问题，核心是用FIRST_VALUE时没做行过滤，或者错误地把EnteredDate加入了分组——因为同一Shipment会有多个不同的EnteredDate，分组包含它的话自然会返回多行。下面给你几种靠谱的解决办法：

方法1：用ROW_NUMBER()过滤最新行 #

给每个Shipment的记录按EnteredDate倒序编号，只保留行号为1的记录，这样每个Shipment只会留最新的那一行：

WITH RankedShipments AS (
    SELECT 
        a.Shipment,
        b.Source,
        a.EnteredDate,
        ROW_NUMBER() OVER (PARTITION BY a.Shipment ORDER BY a.EnteredDate DESC) AS rn
    FROM TableA a
    JOIN TableB b ON a.Shipment = b.Shipment
)
SELECT Shipment, Source, EnteredDate AS [Last Entered Date]
FROM RankedShipments
WHERE rn = 1;

方法2：用聚合函数MAX()先取最新日期再关联 #

先从TableA里按Shipment分组拿到最大的EnteredDate，再和TableB关联，这种写法逻辑更直观：

SELECT 
    a.Shipment,
    b.Source,
    a.LastEnteredDate AS [Last Entered Date]
FROM (
    SELECT Shipment, MAX(EnteredDate) AS LastEnteredDate
    FROM TableA
    GROUP BY Shipment
) a
JOIN TableB b ON a.Shipment = b.Shipment;

注：如果同一个Shipment在TableB里有多个Source值，需要额外处理（比如取第一个或聚合），但假设每个Shipment在TableB里唯一对应一个Source。

方法3：用DISTINCT配合窗口函数（适配你原来的写法） #

如果你想保留窗口函数的写法，只需在查询外层加上DISTINCT，因为同一Shipment的FIRST_VALUE结果是相同的，去重后就能得到唯一行：

SELECT DISTINCT
    a.Shipment,
    b.Source,
    FIRST_VALUE(a.EnteredDate) OVER (PARTITION BY a.Shipment ORDER BY a.EnteredDate DESC) AS [Last Entered Date]
FROM TableA a
JOIN TableB b ON a.Shipment = b.Shipment;

内容的提问来源于stack exchange，提问作者Amadeus