如何将来自不同子查询的多个 SUM 添加到一个结果中？
如何将 MySQL 中不同子查询的 SUM 相加？
加入（ SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM Product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)
加入 ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM Product2_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)
所以我想将这两个相加，让 numQuotes 成为总 numQuotes。然而，它比这更复杂一点，因为不同表的数量是动态的，所以在任何给定的情况下都可能有任意数量的子查询。
How can I add up SUMs from different subqueries in MySQL?
JOIN (
SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM product1_quote GROUP BY customer_id
) p1q ON (p1q.customer_id = c.customer_id)
JOIN (
SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes, customer_id FROM product2_quote GROUP BY customer_id
) p1q ON (p1q.customer_id = c.customer_id)
So I'd want to add those two up and have numQuotes be the total numQuotes. However, it's a little more complicated than that, because the number of different tables is dynamic, so in any given circumstance there could be any number of subqueries.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
下面的结果是什么？
括号可能会被关闭，因此请先检查它们。
What comes up with the following?
Parentheses might be off so check them first.
我通过将 JOIN 更改为 LEFT JOIN 并在将查询放在一起的 PHP 循环内使用
IFNULL(numQuotes".$k.",0)+解决了这个问题，其中$k是一个索引。因此最终结果类似于：
SELECT IFNULL(numQuotes0,0)+IFNULL(numQuotes1,0) AS totalQuotes FROM ...LEFT JOIN ( SELECT SUM(IF(isPurchased='0) ', 1, 0)) AS numQuotes0, customer_id FROM Product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)LEFT JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes1, customer_id FROM Product2_quote GROUP BY customer_id ) p2q ON (p2q.customer_id = c.customer_id)
LEFT JOIN 返回 NULL 如果未找到任何结果，因此需要
IFNULL。I've solved it by changing the JOINs to LEFT JOINs and using
IFNULL(numQuotes".$k.",0)+inside the PHP loop that puts the queries together, where$kis an index.So the end result is something like:
SELECT IFNULL(numQuotes0,0)+IFNULL(numQuotes1,0) AS totalQuotes FROM ...LEFT JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes0, customer_id FROM product1_quote GROUP BY customer_id ) p1q ON (p1q.customer_id = c.customer_id)LEFT JOIN ( SELECT SUM(IF(isPurchased='0', 1, 0)) AS numQuotes1, customer_id FROM product2_quote GROUP BY customer_id ) p2q ON (p2q.customer_id = c.customer_id)
The LEFT JOINs return NULL if no results are found, hence the need for
IFNULL.