php - MySQL 连接并排除 count=0 的行

Question

我有以下简单的查询，它是垃圾收集脚本的一部分。该脚本应该删除未使用的购物车。如果购物车在 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 的购物车，我不明白为什么。有什么线索吗？

Answer 1

我怀疑你正在沿着两个不同的维度加入。简单的解决方法是使用 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 isn't:

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 )