寻求ANSI SQL替代T-SQL中TOP 1 WITH TIES的解决方案

直接给出适配ANSI SQL的完整查询代码，核心是用RANK()窗口函数替代T-SQL特有的TOP 1 WITH TIES，完美保留“同最新日期的所有记录都保留”的需求：

SELECT
   LastDate,
   SiteID,
   UnitsInOuter,
   GroupDescription,
   PLUID,
   Description,
   Cost,
   NULL AS Price,
   NULL AS Units,
   NULL AS RetailValue
FROM (
    SELECT
       LastCostDate AS LastDate,
       SiteID,
       [d1].UnitsInOuter,
       [PLUGroup2].Description AS GroupDescription,
       [d1].PLUID,
       [d1].Description,
       Cost,
       -- 用ANSI标准的RANK()窗口函数计算分组内排名，对应原WITH TIES逻辑
       RANK() OVER (PARTITION BY [d1].Description, SiteID ORDER BY LastCostDate DESC) AS rnk
    FROM
      (select   LastCostDate,
            [LocStock].SiteID,
            [LastDate].UnitsInOuter,
            [LastDate].PLUID,
            [LastDate].Description,
            [LastCost].Cost
    From    (select max([PLUCostHistory].ChangeDate) as LastCostDate,max(PLUOuterSize.UnitsInOuter) as UnitsInOuter, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
            on [PLU].PLUID=[PLUCostHistory].PLUID Inner Join PLUOuterSize on PLUOuterSize.PLUOuterSizeID=PLUCostHistory.PLUOuterSizeID group by [PLUCostHistory].PLUID, [PLU].Description ) as LastDate

    Inner Join (select [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description, sum([PLUCostHistory].Cost) as Cost From [PLUCostHistory] Inner Join [PLU]
            on [PLU].PLUID=[PLUCostHistory].PLUID group by [PLUCostHistory].ChangeDate, [PLUCostHistory].PLUID, [PLU].Description ) as LastCost
    On      LastDate.Description=LastCost.Description
    And     LastDate.LastCostDate=LastCost.ChangeDate
    And     LastDate.PLUID=LastCost.PLUID
    Inner Join [LocStock]
            on [LocStock].PLUID=[LastDate].PLUID

    UNION

    select  LastCostDate,
            [DelMast].SiteID,       
            [LastDate].UnitsInOuter,
            [LastDate].PLUID,
            [LastDate].Description,
            [LastCost].Cost
    From    (select max([DelMast].DeliveryDate) as LastCostDate,max(PLUOuterSize.UnitsInOuter) as UnitsInOuter, [DelDets].PLUID, [PLU].Description From [DelMast] Inner Join [DelDets]
            on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU] on [PLU].PLUID=[DelDets].PLUID Inner Join [PLUGroupRef]
            on [DelDets].PLUID=[PLUGroupRef].PLUID Inner Join [PLUGroup2]
            on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID Inner Join PLUOuterSize on PLUOuterSize.PLUOuterSizeID=DelDets.PLUOuterSizeID group by [DelDets].PLUID, [PLU].Description) as LastDate

    INNER Join (select [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description, sum([DelDets].Cost) as Cost From [DelMast] Inner Join [DelDets] on [DelDets].DeliveryID=[DelMast].DeliveryID Inner Join [PLU]
            on [PLU].PLUID=[DelDets].PLUID group by [DelMast].DeliveryDate, [DelDets].PLUID, [PLU].Description) as LastCost
    On      LastDate.Description=LastCost.Description
    And     LastDate.LastCostDate=LastCost.DeliveryDate
    And     LastDate.PLUID=LastCost.PLUID
    Inner Join [LocStock]
            on [LocStock].PLUID=[LastDate].PLUID
    Inner Join [DelMast] 
            on [LocStock].SiteID=[DelMast].SiteID   ) as d1
    Inner Join [PLUGroupRef]
            on [PLUGroupRef].PLUID=[d1].PLUID
    Inner Join [PLUGroup2]
            on [PLUGroup2].PLUGroup2ID=[PLUGroupRef].PLUGroup2ID

    /*where SiteID like '%BALYS%' and [PLUGroup2].Description like '%TOBACCO%' */
) ranked_data
WHERE rnk = 1
-- 可选：如果需要和原查询排序一致，加上这行
ORDER BY [Description], SiteID, LastDate DESC

关键改动说明 #

1. 移除T-SQL专属语法：删掉SELECT TOP 1 WITH TIES，改用ANSI标准的窗口函数实现逻辑
2. 用RANK()替代原排序逻辑：
   • 在子查询中新增RANK() OVER (PARTITION BY [d1].Description, SiteID ORDER BY LastCostDate DESC) AS rnk
   • RANK()会给同一个(Description, SiteID)分组内、LastCostDate相同的记录分配相同的排名（都是1），完美对应原WITH TIES保留所有同最新日期记录的需求
   • 如果用ROW_NUMBER()会给同日期的记录分配不同序号，会丢失部分数据，不符合需求
3. 过滤排名为1的记录：外层查询通过WHERE rnk = 1筛选出每个分组的最新记录集合

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