从两个表的结果中得到几个列的和是不正确的。
从两个表的结果中得到几个列的和是不正确的。
提问于 2015-03-17 11:24:36
我试图从不同的表中得到这两列的总和，但是我在堆栈中找到了很好的帖子。其中一些人帮了我。但我还是解决不了这个问题。
下面的查询以某种方式返回库仑之和的不正确总数，( rate列)
mysql> Tbl as_servicetickets;
+----------+----------+
|ticket_id | rate |
+----------+----------+
| 11 | 250.00 |
| 11 | 300.00 |
| 11 | 400.00 |
| 9 | 300.00 |
| 9 | 300.00 |
| 9 | 1500.00 |
| 9 | 250.00 |
+----------+----------+
total is 2 350.00
mysql> Tbl as_ticketmaterials;
+----------+---------------+
|ticket_id | materialprice |
+----------+---------------+
| 11 | 100 |
| 9 | 20 |
| 9 | 50 |
+----------+---------------+
total is 70.00查询
SELECT SUM(`as_servicetickets`.`rate`) AS `sercnt`, SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
FROM `as_servicetickets`, `as_ticketmaterials`
WHERE `as_servicetickets`.`ticket_id` = 9
AND `as_ticketmaterials`.`ticket_id` = 9
GROUP BY `as_servicetickets`.`ticket_id`, `as_ticketmaterials`.`ticket_id
result ------------------///// this is not correct
+---------+--------+
| sercnt | sercnt |
+---------+--------+
| 4700.00 | 280 |
+---------+--------+回答 3
Stack Overflow用户
回答已采纳
发布于 2015-03-17 11:32:26
这不是达到预期结果的正确方法。试一试：-
SELECT (SELECT SUM(`as_servicetickets`.`rate`) AS `sercnt`
FROM `as_servicetickets`
WHERE `as_servicetickets`.`ticket_id` = 9),
(SELECT SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
FROM `as_ticketmaterials`
WHERE `as_ticketmaterials`.`ticket_id` = 9);Stack Overflow用户
发布于 2015-03-17 11:32:04
尝试使用显式联接，因为不鼓励隐式联接(条件有问题的地方)
SELECT `as_servicetickets`.`ticket_id`, SUM(`as_servicetickets`.`rate`) AS `sercnt`, SUM(`as_ticketmaterials`.`materialprice`) AS `sercnt`
FROM `as_servicetickets` INNER JOIN `as_ticketmaterials`
ON `as_servicetickets`.`ticket_id` = `as_ticketmaterials`.`ticket_id`
WHERE `as_servicetickets`.`ticket_id` = 9
GROUP BY `as_servicetickets`.`ticket_id`Stack Overflow用户
发布于 2015-03-17 11:36:52
select sum(a.rate) as sercnt, sum(b.materialprice) as sercnt from
as_servicetickets a inner join as_ticketmaterials b on
a.ticket_id = b.ticket_id where a.ticket_id = 9 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29097827
复制相关文章
相似问题
腾讯云开发者
