我有以下简单查询,它是垃圾回收脚本的一部分。该脚本应删除未使用的购物车。超过24小时前更新的购物车尚未使用,并且未附加到订单或用户。
$query = "SELECT comm_cart.id AS `cart_id`, (" . time() . " - comm_cart.update_date) AS `diff`, COUNT(comm_orders.cart_id) AS `c1`, COUNT(comm_users.cart_id) AS `c2` " .
"FROM `comm_cart` " .
"LEFT JOIN `comm_orders` ON comm_cart.id=comm_orders.cart_id " .
"LEFT JOIN `comm_users` ON comm_cart.id=comm_users.cart_id " .
"GROUP BY comm_cart.id ";
"HAVING `diff`>86400 AND `c1`=0 AND `c2`=0";
该查询找到了太多的购物车:它还标记了c1> 0或c2> 0的购物车,我不知道为什么。有什么线索吗?
最佳答案
我怀疑您正在沿着两个不同的维度加入。简单的解决方法是使用distinct
:
SELECT comm_cart.id AS `cart_id`, (" . time() . " - comm_cart.update_date) AS `diff`,
COUNT(DISTINCT comm_orders.cart_id) AS `c1`, COUNT(DISTINCT comm_users.cart_id) AS `c2` " .
更好的解决方案是对这两个条件使用
not exists
:FROM comm_carts cc
WHERE not exists (select 1 from comm_orders co where cc.id = co.cart_id )
not exists (select 1 from comm_users cu where cc.id = cu.cart_id )
关于php - MySQL Join并排除count = 0的行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/24409552/