减少SQL Server中的查询冗余

Question

SELECT
    invoiceNo AS Bill_No,
    SUM(ISNULL((OFI.[New Quantity] * OFI.[Unit Sell Price]),0)) AS Amount,
    SUM(ISNULL(((OFI.[New Quantity] * OFI.[Unit Sell Price]) + FBP.[Packing/Delivery Charges] + FBP.[Miscellaneous Charge]) * (FBP.[Additional Discount Percent] / 100), 0)) AS Discount 
FROM
    dbo.[Final Payment Bill] FBP
JOIN
    dbo.[Order] o ON o.finalPaymentBill_invoiceNo = FBP.invoiceNo
JOIN
    dbo.[Ordered Food Item] OFI ON o.[Order Number] = OFI.Order_orderNo
GROUP BY
    FBP.invoiceNo

从查询中可以看到，为了计算，我必须编写两次相同的求和函数。有别的办法让我不用再写了吗？

PS:我不确定用什么方法来回答这个问题。

Answer 1

我相信您可以对此使用子查询。

select 
   invoiceNo as Bill_No,
   sum(FinalPrice) as Amount,
   sum(ISNULL((FinalPrice + FBP.[Packing/Delivery Charges] + FBP.[Miscellaneous Charge]) * (FBP.[Additional Discount Percent] / 100) ,0)) as Discount 
from dbo.[Final Payment Bill] FBP
join dbo.[Order] o
    on o.finalPaymentBill_invoiceNo = FBP.invoiceNo
join (select Order_orderNo, (OFI.[New Quantity] * OFI.[Unit Sell Price]) FinalPrice from dbo.[Ordered Food Item]) OFI
    on o.[Order Number] = OFI.Order_orderNo
group by FBP.invoiceNo

和CTE以提高可读性

with OFI as
(
   select Order_orderNo, (OFI.[New Quantity] * OFI.[Unit Sell Price]) FinalPrice 
   from dbo.[Ordered Food Item]
)
select 
   invoiceNo as Bill_No,
   sum(FinalPrice) as Amount,
   sum(ISNULL((FinalPrice + FBP.[Packing/Delivery Charges] + FBP.[Miscellaneous Charge]) * (FBP.[Additional Discount Percent] / 100) ,0)) as Discount 
from dbo.[Final Payment Bill] FBP
join dbo.[Order] o
    on o.finalPaymentBill_invoiceNo = FBP.invoiceNo
join OFI
    on o.[Order Number] = OFI.Order_orderNo
group by FBP.invoiceNo

Answer 2

复制没什么大不了的。但是，您可以简化代码。SUM()忽略NULL值，因此这可能会实现您想要的结果：

select invoiceNo as Bill_No,
       sum(OFI.[New Quantity] * OFI.[Unit Sell Price]) as Amount,
       sum(OFI.[New Quantity] * OFI.[Unit Sell Price]) + FBP.[Packing/Delivery Charges] + FBP.[Miscellaneous Charge]) * (FBP.[Additional Discount Percent] / 100) as Discount 
from dbo.[Final Payment Bill] FBP join
     dbo.[Order] o
     on o.finalPaymentBill_invoiceNo = FBP.invoiceNo join
     dbo.[Ordered Food Item] OFI
     on o.[Order Number] = OFI.Order_orderNo
group by FBP.invoiceNo;

您可以使用CTE或子查询(或者在其他情况下使用apply)来避免重复。但在这种情况下，这种努力似乎不值得。

一个更重要的问题是，+没有忽略NULL值，所以如果任何FPB列都是NULL，那么该行的所有值都会被忽略。

Answer 3

你在那里的重复没有问题。但是，如果在[Ordered Food Item]表上创建了计算列，则可以将其删除。

ALTER TABLE [Ordered Food Item] 
ADD Amount 
AS ([New Quantity] * [Unit Sell Price])