如何嵌套UNION查询获取合并表中产品最新成本及变更日期？

我已在公司SQL Server数据库中编写了UNION查询，分别提取产品的最新成本和最新配送价并合并。现在需要将该UNION查询嵌套，从合并后的结果中获取每个产品最后一次成本变更的日期及对应成本。

现有UNION查询代码 #

select  LastCostDate,
        [LocStock].SiteID,      
        [LastDate].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([PLUCostHistory].ChangeDate) as LastCostDate, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
        on [PLU].PLUID=[PLUCostHistory].PLUID 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].PLUID,
        [LastDate].Description,
        [LastCost].Cost
From    (select max([DelMast].DeliveryDate) as LastCostDate, [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 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

示例数据表 #

表1（产品成本历史） #

表2（配送价历史） #

期望输出 #

解决方案 #

可以将现有UNION查询作为子查询，通过分组筛选每个产品的最新变更日期，再关联子查询获取对应成本。推荐使用CTE（公共表表达式）简化代码，避免重复编写UNION逻辑：

WITH CombinedCosts AS (
    -- 原UNION查询内容
    select  LastCostDate,
            [LocStock].SiteID,      
            [LastDate].PLUID,
            [LastDate].Description,
            [LastCost].Cost
    From    (select max([PLUCostHistory].ChangeDate) as LastCostDate, [PLUCostHistory].PLUID, [PLU].Description From [PLUCostHistory] Inner Join [PLU]
            on [PLU].PLUID=[PLUCostHistory].PLUID 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].PLUID,
            [LastDate].Description,
            [LastCost].Cost
    From    (select max([DelMast].DeliveryDate) as LastCostDate, [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 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
)
SELECT cc.LastCostDate, cc.Description, cc.Cost
FROM CombinedCosts cc
INNER JOIN (
    -- 按产品分组获取最新变更日期
    SELECT Description, MAX(LastCostDate) AS LatestDate
    FROM CombinedCosts
    GROUP BY Description
) AS latest
ON cc.Description = latest.Description AND cc.LastCostDate = latest.LatestDate

代码说明 #

1. CombinedCosts CTE：存储原UNION查询的合并结果，包含所有产品的成本/配送价记录及对应日期
2. 子查询latest：按产品分组，提取每个产品的最后一次变更日期
3. 最终关联：将CTE结果与latest子查询关联，过滤出每个产品最新变更日期对应的成本数据

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