T-SQL聚合子查询更新报错：agg.SalesDate无法绑定

问题根源 #

你遇到的agg.SalesDate无法绑定错误，是因为JOIN后的派生表（子查询）是独立执行的，它的作用域里无法访问外层查询的agg别名对应的列。外层WHERE子句能正常引用agg.SalesDate，是因为那是直接对主表Agg_Sales_By_Department的过滤，和子查询不在同一个作用域。

解决方案 #

以下两种方法都能解决这个问题，可根据数据量选择合适的方案：

方案1：使用关联子查询直接赋值 #

把计算逻辑放到SET的赋值语句中，这样子查询可以关联外层的每一行agg数据：

DECLARE @AsOfDate DATE, @ToDate DATE;
SELECT @AsOfDate = '1/1/2025', @ToDate = '1/31/2025';

UPDATE agg
SET MTD_Sales_LY = ISNULL(
    (SELECT SUM(s.NetPrice)
     FROM FactSales s 
     JOIN DimDate dt ON s.OrderDateID = dt.DateSK
     WHERE s.StoreID = agg.StoreID
       AND s.DepartmentTypeID = agg.DepartmentTypeID
       AND dt.[Date] BETWEEN DATEFROMPARTS(YEAR(agg.SalesDate), MONTH(agg.SalesDate), 1) AND agg.SalesDate
    ), 0),
    MTP_Orders_LY = ISNULL(
    (SELECT COUNT(s.ID)
     FROM FactSales s 
     JOIN DimDate dt ON s.OrderDateID = dt.DateSK
     WHERE s.StoreID = agg.StoreID
       AND s.DepartmentTypeID = agg.DepartmentTypeID
       AND dt.[Date] BETWEEN DATEFROMPARTS(YEAR(agg.SalesDate), MONTH(agg.SalesDate), 1) AND agg.SalesDate
    ), 0)
FROM Agg_Sales_By_Department agg
WHERE agg.SalesDate BETWEEN @AsOfDate AND @ToDate;

注：把原来的日期字符串拼接改成DATEFROMPARTS，避免因日期格式差异导致的错误，更稳定可靠。

方案2：使用CROSS APPLY（SQL Server专属） #

CROSS APPLY允许子查询引用外层查询的列，因为它是逐行关联处理主表数据的：

DECLARE @AsOfDate DATE, @ToDate DATE;
SELECT @AsOfDate = '1/1/2025', @ToDate = '1/31/2025';

UPDATE agg
SET MTD_Sales_LY = ISNULL(x.TotalSales, 0),
    MTP_Orders_LY = ISNULL(x.TotalOrders, 0)
FROM Agg_Sales_By_Department agg
CROSS APPLY (
    SELECT SUM(s.NetPrice) TotalSales, COUNT(s.ID) TotalOrders
    FROM FactSales s 
    JOIN DimDate dt ON s.OrderDateID = dt.DateSK
    WHERE s.StoreID = agg.StoreID
      AND s.DepartmentTypeID = agg.DepartmentTypeID
      AND dt.[Date] BETWEEN DATEFROMPARTS(YEAR(agg.SalesDate), MONTH(agg.SalesDate), 1) AND agg.SalesDate
    GROUP BY s.StoreID, s.DepartmentTypeID
) x
WHERE agg.SalesDate BETWEEN @AsOfDate AND @ToDate;

为什么原写法无效？ #

JOIN的派生表会在主查询执行前先计算完成，此时外层的agg别名还没有和主表数据关联，所以子查询无法识别agg.SalesDate。而CROSS APPLY或关联子查询是针对主表的每一行单独执行子查询，因此能访问到当前行的SalesDate值。

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