mysql - SQL:选择与其父查询相反的子查询 where 条件

Question

invoice_payments

invoice_id amount type
         1  10.00 Cash
         1   5.00 Cash
         1   5.00 Cash
         2  70.00 Store
         2  30.00 Cash

我想获取总金额为现金的行和总金额为 type = store 的行

我正在使用这个查询:

SELECT invoice_id, SUM( amount ) AS total_paid, (
SELECT SUM( amount )  FROM invoice_payments WHERE TYPE =  'store' ) AS total_store
FROM invoice_payments  where type!='Store'
GROUP BY invoice_id

结果:

invoice_id total_paid total_store
         1      20.00       70.00
         2     100.00       70.00

期望的结果:

invoice_id total_paid total_store
         1      20.00       0.00
         2     100.00       70.00

如您所见，total_store 字段的值不正确。请提出最佳解决方案。

非常感谢任何回复。

谢谢

Answer 1

选择 invoice_id, (select sum(amount) from payments p where p.invoice_id = a.invoice_id) 'total_paid', (从 payments c 中选择 sum(amount) where c.invoice_id = a.invoice_id and type = 'store') 'total_store' from payments a按 invoice_id

分组