php - mysql 在同一个查询上有和 where 子句

Question

这是我的代码:

$sqlz = "SELECT t1.user_id, t2.status, t2.email 
         FROM coverages t1 
         LEFT JOIN users t2 ON t1.user_id = t2.user_id 
         GROUP BY t1.user_id  
         HAVING COUNT(t1.user_id) =".$value;

我想添加这个“WHERE users.email IS NOT NULL”当我添加它时，它返回一个白页/没有结果。我知道事实上数据库中至少有 200 个结果包含电子邮件并符合该条件。

这是我所做的不起作用的示例:

 $sqlz =    "SELECT t1.user_id, t2.status, t2.email 
             FROM coverages t1 
             LEFT JOIN users t2 ON t1.user_id = t2.user_id
             WHERE users.email IS NOT NULL 
             GROUP BY t1.user_id  
             HAVING COUNT(t1.user_id) =".$value;

Answer 1

我认为您需要使用 t2(别名)而不是 users。

 $sqlz =    "SELECT t1.user_id, t2.status, t2.email 
             FROM coverages t1 
                     LEFT JOIN users t2 ON t1.user_id = t2.user_id
             WHERE t2.email IS NOT NULL 
             GROUP BY t1.user_id  
             HAVING COUNT(t1.user_id) = " .$value;