SQL Server中计算多产品时间段价格变动的SQL语句求助

本人具备Oracle SQL与MySQL基础，首次使用SQL Server，需计算多款产品的价格变动值。

现有表结构（Production.ProductCostHistory） #

期望查询结果 #

本人编写了如下CTE查询语句，但发现SQL Server中WHERE...IN的用法与Oracle SQL存在差异（不支持多列匹配），未能找到适配需求的EXISTS用法，特求助修正该语句。注：平均计算将在PowerBI中完成，无需在SQL查询中实现。

with a as
    (
        select 
            productID, standardcost, startDate, ISNULL(endDate, GETDATE())
        from
            production.ProductCostHistory 
        where (productID, endDate) in (select 
                              productID, max(endDate)
                          from 
                              Production.ProductCostHistory
                          group by
                              productID
                         )
    ), 
b as 
    (
        select 
            productID, standardcost, startDate, startDate
        from
            production.ProductCostHistory 
        where (productID, startDate) in (select 
                              productID, min(StartDate)
                          from 
                              Production.ProductCostHistory
                          group by
                              productID
                         )
    )

select 
    a.productID, (a.standardcost - b.standardcost) difference  
from
     a join b
    on a.ProductID = b.ProductID and 
       a.startDate = b.startDate and
       a.EndDate = b.EndDate

问题核心 #

SQL Server不支持Oracle/MySQL中的(列1, 列2) IN (子查询)多列匹配语法，且原查询的JOIN条件存在逻辑错误（首尾记录的日期不可能完全一致），导致无法正确关联数据。

方案1：使用JOIN替代多列IN #

通过JOIN获取每个产品的首次和末次成本记录，再计算差值：

WITH FirstCost AS (
    SELECT 
        p.ProductID,
        p.StandardCost AS FirstStandardCost
    FROM Production.ProductCostHistory p
    JOIN (
        SELECT ProductID, MIN(StartDate) AS MinStartDate
        FROM Production.ProductCostHistory
        GROUP BY ProductID
    ) f ON p.ProductID = f.ProductID AND p.StartDate = f.MinStartDate
),
LastCost AS (
    SELECT 
        p.ProductID,
        p.StandardCost AS LastStandardCost
    FROM Production.ProductCostHistory p
    JOIN (
        SELECT ProductID, MAX(ISNULL(EndDate, GETDATE())) AS MaxEndDate
        FROM Production.ProductCostHistory
        GROUP BY ProductID
    ) l ON p.ProductID = l.ProductID 
        AND ISNULL(p.EndDate, GETDATE()) = l.MaxEndDate
)
SELECT 
    f.ProductID,
    (l.LastStandardCost - f.FirstStandardCost) AS Difference
FROM FirstCost f
JOIN LastCost l ON f.ProductID = l.ProductID;

方案2：使用EXISTS替代多列IN #

针对原CTE思路，用EXISTS改写多列匹配逻辑：

WITH a AS (
    SELECT 
        ProductID, 
        StandardCost, 
        StartDate, 
        ISNULL(EndDate, GETDATE()) AS EndDate
    FROM Production.ProductCostHistory p
    WHERE EXISTS (
        SELECT 1
        FROM Production.ProductCostHistory
        WHERE ProductID = p.ProductID
        GROUP BY ProductID
        HAVING MAX(ISNULL(EndDate, GETDATE())) = ISNULL(p.EndDate, GETDATE())
    )
),
b AS (
    SELECT 
        ProductID, 
        StandardCost, 
        StartDate
    FROM Production.ProductCostHistory p
    WHERE EXISTS (
        SELECT 1
        FROM Production.ProductCostHistory
        WHERE ProductID = p.ProductID
        GROUP BY ProductID
        HAVING MIN(StartDate) = p.StartDate
    )
)
SELECT 
    a.ProductID,
    (a.StandardCost - b.StandardCost) AS Difference
FROM a
JOIN b ON a.ProductID = b.ProductID;

方案3：使用窗口函数（更简洁高效） #

利用FIRST_VALUE和LAST_VALUE窗口函数直接获取每个产品的首尾成本，仅需扫描一次表：

WITH CostWithBounds AS (
    SELECT 
        ProductID,
        StandardCost,
        FIRST_VALUE(StandardCost) OVER (PARTITION BY ProductID ORDER BY StartDate) AS FirstCost,
        LAST_VALUE(StandardCost) OVER (
            PARTITION BY ProductID 
            ORDER BY StartDate 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) AS LastCost
    FROM Production.ProductCostHistory
)
SELECT DISTINCT
    ProductID,
    (LastCost - FirstCost) AS Difference
FROM CostWithBounds;

说明 #

• 方案3的窗口函数写法效率最优，适合数据量较大的场景。
• 处理EndDate为NULL的记录时，用ISNULL(EndDate, GETDATE())将其视为当前有效记录，符合业务逻辑。

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