使用求和计算，查询不工作？

Question

我有两张桌子。

a) ai_account

​

​

b) ai_order_product

​

​

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

1，totalAmount，我想做一些像SUM(ai_order_product.quantity * ai_order_product.cost)之类的事情。

2，amountPaid，这是供应商支付的总额，与supplier_id的关系类似于SUM(ai_account.amount)。

3)余额，这将由SUM(ai_order_product.quantity * ai_order_product.cost) - SUM(ai_invoice.amount)计算。

4) lastPayment日期，即最大值(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

这可能有点偏离，但是这个一般的逻辑应该是有效的。

Answer 2

在使用聚合函数(如在Select语句中使用SUM )时，必须使用来声明组。

SELECT op.supplier_id as supplierId,
       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)
    GROUP BY op.supplier_id
    HAVING supplierId = 42

Answer 3

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

当您选择多个group by时，supplier_id子句就会启动。