從兩個連接表中選擇 Sum

有以下結構：

CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `invoices` VALUES (1,'2018-09-22');

CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`quantity` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `products` VALUES (1,1,150.00,2),(2,1,60.00,3),(3,1,50.00,1);

CREATE TABLE `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `payments` VALUES (1,1,400.00,'2018-09-23'),(2,1,80.00,'2018-09-23');

我有這個查詢：

select i.id, sum(pr.amount * pr.quantity) as productAmount, 
sum(pm.amount) as paymentAmount
from invoices as i
left join products as pr on pr.invoice_id=i.id
left join payments as pm on pm.invoice_id=i.id
group by i.id

並得到這個結果：

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |       1060.00 |       1440.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

但是，我想得到以下結果：

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |        530.00 |        480.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

我想要按 invoice.id 分組的產品總金額和付款總金額。

在這種情況下應該查詢什麼？

通過添加支付連接，其中有兩個，productAmount 增加了一倍：

select i.id, sum(distinct pr.amount * pr.quantity) as productAmount, 
sum(distinct pm.amount) as paymentAmount
from invoices as i
join products as pr on pr.invoice_id=i.id
join payments as pm on pm.invoice_id=i.id
group by i.id;

也替換LEFT JOIN為JOIN您不關心不匹配的條目。

這是另一種選擇：

SELECT ProductAmt.id
   ,productAmount
   ,paymentamt.paymentamount
FROM (
   SELECT i.id
       ,sum(pr.amount * pr.quantity) AS productAmount
   FROM invoices AS i
   LEFT JOIN products AS pr ON pr.invoice_id = i.id
   GROUP BY i.id
   ) ProductAmt
LEFT JOIN (
   SELECT i.id
       ,sum(pm.amount) AS paymentAmount
   FROM invoices AS i
   LEFT JOIN payments AS pm ON pm.invoice_id = i.id
   GROUP BY i.id
   ) PaymentAmt ON productAmt.id = paymentamt.id

SQL Fiddle 範例在這裡

引用自：https://dba.stackexchange.com/questions/218386