如何在 INNER JOIN 中对 SUM 的结果使用聚合 SUM 函数? [英] How to use aggregate SUM function on result of SUM in INNER JOIN?

问题描述

这是我连接两个表的 SQL 查询:

SELECT `o`.`orderID`,SUM(i.partialAmount) AS `amount`FROM `OrderTable` AS `o`INNER JOIN `Item` AS `i`ON i.orderID = o.orderIDGROUP BY `o`.`orderID`

商品通过一对多的关系连接到订单.每个项目都有一个 partialAmount 列，我将这些部分金额相加以获得每个订单的总金额.

不过，我想获得所有订单的总金额，该怎么做?

所以我想要类似的东西:SUM(amount) AS totalAmount 但这不起作用:

SELECT `o`.`orderID`,SUM(i.partialAmount) AS `amount`,SUM(amount) AS `totalAmount`FROM `OrderTable` AS `o`INNER JOIN `Item` AS `i`ON i.orderID = o.orderIDGROUP BY `o`.`orderID`

我不想使用 WITH ROLLUP - 因为我不想多出一行.

预期输出:

-----------------------------------------------------------------------|订单编号 |金额 |总金额 |-----------------------------------------------------------------------|1 |2 |5 |-----------------------------------------------------------------------|2 |3 |5 |-----------------------------------------------------------------------

解决方案

我强烈建议在应用程序中做这种事情而不是 SQL，因为它是重复的工作，但可以通过以下方式实现:

>

SELECT OrderTable.OrderID,SUM(PartialAmount) 作为金额，总金额从订单表，物品，(SELECT SUM(PartialAmount) AS TotalAmount FROM Item) AS totalWHERE Item.OrderID = Ordertable.OrderIDGROUP BY OrderTable.OrderID, TotalAmount

我无法检查执行计划以将其与 Lamak 在另一个答案中发布的 Subselect 选项进行比较，但 SQL Server 确实比 subselect 优化了交叉应用.

This is my SQL query which joins two tables:

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID` 

Items are connected to orders via 1-to-many relationship. Each item has a partialAmount column and I am adding up those partial amounts to get a total amount per each order.

I would like to get a total amount for all orders though, how to do that?

So I would like something like: SUM(amount) AS totalAmount but this doesn't work:

SELECT `o`.`orderID`,
       SUM(i.partialAmount) AS `amount`,
       SUM(amount) AS `totalAmount`
FROM   `OrderTable` AS `o`
       INNER JOIN `Item` AS `i`
         ON i.orderID = o.orderID
GROUP  BY `o`.`orderID` 

I don't want to use WITH ROLLUP - because I don't want an extra row.

Expected output:

-----------------------------------------------------------------------
|   orderID       |        amount      |          totalAmount         |
-----------------------------------------------------------------------
|   1             |          2         |                5             |
-----------------------------------------------------------------------
|   2             |          3         |                5             |
-----------------------------------------------------------------------

解决方案

I'd highly advise doing this kind of thing in the application rather than SQL as it is repitition of work, but it can be achieved as follows:

SELECT  OrderTable.OrderID, 
        SUM(PartialAmount) AS Amount,
        TotalAmount
FROM    OrderTable, 
        Item, 
        (SELECT SUM(PartialAmount) AS TotalAmount FROM Item) AS total
WHERE   Item.OrderID = Ordertable.OrderID
GROUP BY OrderTable.OrderID, TotalAmount

I can't check the execution plan to compare this to the option of the Subselect posted in another answer by Lamak, but SQL Server definitely optimises the cross apply much better than the subselect.

这篇关于如何在 INNER JOIN 中对 SUM 的结果使用聚合 SUM 函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！