代码之家 › 专栏 › 技术社区 › bcmcfc

如何将不同子查询的多个和相加到一个结果中?

mysql

bcmcfc · 技术社区 · 14 年前

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)

所以我想把这两个加起来,让numqutes成为总numqutes。但是,它比这要复杂一些,因为不同表的数量是动态的,所以在任何给定的情况下都可能有任意数量的子查询。

2 回复 | 直到 14 年前

pkauko 14 年前

下面是什么?

select sum(numQuotes), customer_id from
(
  (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)
UNION
  (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)
) group by customer_id;

括号可能已关闭,请先检查它们。

bcmcfc 14 年前

我通过将连接改为左连接并使用 IFNULL(numQuotes".$k.",0)+ 在将查询放在一起的PHP循环中 $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)

如果找不到结果,左连接将返回NULL,因此需要 IFNULL .