错误:窗口函数不能在另一个窗口函数或聚合的上下文中使用

Question

我试图使用下面的查询来获取每张发票的总金额，但我得到了以下错误：

Msg4109，级别15，状态1，第3行开窗函数不能在另一个开窗函数或聚合的上下文中使用。

查询

SELECT
SP.InvoiceNo,
Sum(CAST((SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Shipping_Cost + (SPD.Quantity * SPD.UnitPrice) / (SUM(SPD.Quantity * SPD.UnitPrice) OVER ()) * Sp.Customs_Cost + SPD.Quantity * SPD.UnitPrice - (SPD.Discount / 100) * SPD.Quantity * SPD.UnitPrice AS decimal(10, 2))) AS [Total Amount]
FROM dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
ON SPD.Pro_ID = S.Pro_ID
group by SP.InvoiceNo,SPD.Quantity,SPD.UnitPrice

Answer 1

如错误消息中所述，您不能在sum聚合中使用sum()over()聚合窗口函数。

尝试使用派生表

SELECT InvoiceNo,
Sum(Cast(( sales ) / ( total_sales ) * Shipping_Cost + ( sales ) / ( total_sales ) * Customs_Cost + sales - ( Discount / 100 ) * sales AS DECIMAL(10, 2))) AS [Total Amount]
FROM   (SELECT SP.InvoiceNo,
Sp.Shipping_Cost,
Sp.Customs_Cost,
SPD.Quantity,
SPD.UnitPrice,
Sum(SPD.Quantity * SPD.UnitPrice)
OVER()                     AS total_sales,
SPD.Discount,
SPD.Quantity * SPD.UnitPrice AS sales
FROM   dbo.Stock_Purchase SP
INNER JOIN dbo.Stock_Purchase_Details SPD
ON SP.Purchase_ID = SPD.Purchase_ID
INNER JOIN dbo.Store S
ON SPD.Pro_ID = S.Pro_ID) a
GROUP  BY InvoiceNo,
Quantity,
UnitPrice 

我想通过添加样本数据和预期结果可以改进查询

Answer 2

我相信你想要这样的东西：

SELECT SP.InvoiceNo,
SUM(SPD.Quantity * SPD.UnitPrice * (1 - SP.Discount)) + sp.Shipping_Cost as Total_amount
FROM dbo.Stock_Purchase SP INNER JOIN
dbo.Stock_Purchase_Details SPD
ON SP.Purchase_ID = SPD.Purchase_ID INNER JOIN
dbo.Store S
ON SPD.Pro_ID = S.Pro_ID
GROUP BY SP.InvoiceNo, sp.Shipping_Cost;

注：

• 总成本的计算是有根据的猜测。几乎不可能从代码中获得计算的意图
• 您的GROUP BY的列太多
• 这种计算似乎不需要窗口函数