从两个连接表中选择 Sum

2022-01-09 00:00:00 sum group-by database mysql

有结构:

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)

但是，我想得到以下结果:

However, I want to get the following result:

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

我想要按 invoice.id 分组的产品总金额和付款总金额.
在这种情况下应该查询什么?

I want sum amount of products and sum amount of payments grouped by invoice.id.
What should be the query in this case?

推荐答案

我确实有时会遇到这种查询.由于多个连接，来自特定表的值会重复、三次等.为了解决这个问题，我通常通过将总和(在特定表上)除以不同 Id 的计数来做一个小技巧(s) 来自其他表.这抵消了多次重复发生的影响.

I do face this kind of queries at times. Due to multiple joins, values from a particular table get duplicated, triplicated etc. To fix this, I normally do a small hack by dividing the sum (on a particular table) by the count of distinct Id(s) from the other table. This negates the effect of multiple duplicates happening.

尝试以下查询:

select i.id, (sum(pr.amount * pr.quantity) / IF(count(distinct pm.id) > 0, count(distinct pm.id), 1) as productAmount, (sum(pm.amount) / IF(count(distinct pr.id) > 0, count(distinct pr.id), 1) 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

相关文章