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

b09cbbtk 于 2022-11-21 发布在 SQL Server

关注(0)|答案(1)|浏览(226)

执行时出现错误“多部分标识符“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)

我希望能解决这个问题

sql-server

来源：https://stackoverflow.com/questions/74493821/sql-the-multi-part-identifier-could-not-be-bound-sql-server

1条答案

按热度按时间

yyyllmsg1#

就像Jeff在评论中说的，使用用户定义的函数来聚合Mbse.OrderDetails表的计算列并不是一个好主意，原因有很多。它会很繁重，每一行都会处理一次RBAR（逐行），并且会对任何直接或间接引用该函数或Mbse.orders表的查询执行prevent parallelism。
最好在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;

赞(0）回复(0）举报 2022-11-21

我来回答

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

1条答案

相关问题

热门标签

最新问答