多部分标识符无法绑定sql server。

Question

我在执行“多部分标识符”"od.Ordernumber“不能是”多部分标识符“"od.Location_code”时出现了该错误。

create function Mbse.udf_ordertotal
(@Numberoforder int , @loction_code int )
returns int 
as
begin

declare @amount as int 

set @amount=(select  sum(od.amount) from Mbse.OrderDetails as od 
where (@Numberoforder=od.Ordernumber and @loction_code=od.Location_code) 
)
return @amount
end

alter table Mbse.orders
add amount as Mbse.udf_ordertotal(Mbse.OrderDetails.Ordernumber , Mbse.OrderDetails.location_code)

我希望这个问题能得到解决

Answer 1

正如Jeff在评论中所说的那样，使用用户定义的函数来聚合Mbse.OrderDetails表的计算列由于多种原因而不是一个好主意。它将很重，将处理RBAR (按痛苦的行排列)，即每一行一次，并将防止并行用于直接或间接引用该函数或Mbse.orders表的任何查询。

最好对OrderDetails表进行适当的索引，并在视图中将其连接到Orders表，如下所示：

-- Columnstore indexes are typically very quick for aggregative queries
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_OrderDetails_Amount ON Mbse.OrderDetails (Ordernumber, Location_code, amount);

CREATE VIEW Mbse.OrdersWithTotals 
AS

WITH _OrderDetailsTotals AS
(
    SELECT
        Ordernumber, 
        Location_code, 
        SUM(amount) AS TotalAmount
    FROM Msbe.OrderDetails
    GROUP BY 
        Ordernumber, 
        Location_code
)

SELECT
    O.Ordernumber,
    O.location_code,
    ODT.TotalAmount
FROM Mbse.orders AS O
LEFT JOIN _OrderDetailsTotals AS ODT
    ON O.Ordernumber = ODT.Ordernumber
    AND O.location_code = ODT.Location_code;