有以下结构:
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分组的产品总额和付款总额。
在这种情况下,查询应该是什么?
4条答案
按热度按时间cetgtptt1#
使用下面的子查询获得预期结果
ecfdbz9o2#
我有时确实会遇到这种问题。由于有多个连接,来自一个特定表的值会被复制、三倍等等。为了解决这个问题,我通常会做一个小的改动,将总和(在一个特定表上)除以来自另一个表的不同id的计数。这抵消了发生多个重复的影响。
尝试以下查询:
odopli943#
试试这个:https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3b496214bf0ffb517dcaa9be7f0b6bb7
wnavrhmk4#
我可以想象这样一种情况:创建了一张发票,创建了一个产品,但没有付款,类似地,创建了一张发票,创建了一个付款,但没有产品,所以您可以为产品和付款创建子查询
你可以(也可以不想要having子句)