mysql - 使用 SUM 计算，查询不起作用？

Question

我有两个表。

a) ai_account

b) ai_order_product

我想为特定的 supplier_id 做一些计算。

1, totalAmount, i want to do something like SUM(ai_order_product.quantity * ai_order_product.cost)

2, amountPaid, this is total amount paid by the supplier that will be something like SUM(ai_account.amount) with reference to supplier_id.

3) balance, this will be calculated by SUM(ai_order_product.quantity * ai_order_product.cost) - SUM(ai_invoice.amount)

4) lastPayment date, that will be MAX(ai_account.addDate).

我试过做这样的事情。

SELECT SUM(op.quantity * op.cost) as totalAmount, 
       SUM(ac.amount) as amountPaid, 
       SUM(op.quantity * op.cost) - SUM(ac.amount) as balance, 
       MAX(ac.addDate) as lastPayment 
    FROM ai_order_product op 
        LEFT JOIN ai_account ac 
            ON (op.supplier_id = ac.trader_id) 
    WHERE op.supplier_id = 42

它不能正常工作，它获取了一些意想不到的值，而上述结果是预期的，

for supplier_id = 42,
1) totalAmount = 1375,
2) amountPaid = 7000,
3) balance = -5625,
4) lastPayment = 2011-11-23

and for supplier_id = 35,
1) totalAmount = 1500,
2) amountPaid = 43221,
3) balance = -41721,
4) lastPayment = 2011-11-28

and for supplier_id = 41
1) totalAmount = 0
2) amountPaid = 3000,
3) balance = -3000,
4) lastPayment = 2011-11-09

我想通过 supplier_id 获取一行。

P.S:我只是输入了一些虚拟值，这就是为什么计算结果大多为负数，而在应用程序中计算的值将为正数。

Answer 1

那是因为每个“ai_order_product”行都被计算了多次(ai_account 表中的每一行计算一次)。

试试这个:

SELECT 
  op.totalAmount as totalAmount
  , SUM(ac.amount) as amountPaid
  , op.totalAmount - SUM(ac.amount) as balance
  , MAX(ac.addDate) as lastPayment 
FROM (
  select supplier_id, sum(quantity * cost) as totalAmount 
  from ai_order_product
  group by supplier_id) op 
LEFT JOIN ai_account ac ON (op.supplier_id = ac.trader_id) 
WHERE op.supplier_id = 42

这可能有点不对劲，但这个一般逻辑应该可行。