mysql - SQL JOIN 上的 WHERE 子句

Question

我有以下查询来返回客户获得的工作(访问)数量以及他/她使用过的不同 vert 。

SELECT 
customer.users_id AS CustID, 
COUNT(VisitID) AS Jobs, 
COUNT(DISTINCT VetID) AS Resources 
FROM customer 
LEFT JOIN visit ON customer.users_id = visit.CustID 
GROUP BY customer.users_id

当我添加 WHERE 子句以仅计算未重新安排或取消的访问时，我的查询如下所示。

SELECT 
customer.users_id AS CustID, 
COUNT(VisitID) AS Jobs, 
COUNT(DISTINCT VetID) AS Resources 
FROM customer LEFT JOIN visit ON customer.users_id = visit.CustID 
WHERE visit.Status != 'Cancelled' AND visit.Status != 'ReScheduled' 
GROUP BY customer.users_id

但是，在这种情况下，我只得到没有任何取消或重新安排访问的客户，尽管我对客户使用左连接。

我如何适应这个 WHERE 子句？

Answer 1

您不需要 WHERE 子句。这只是将 LEFT JOIN 转换为 INNER JOIN。您需要在 LEFT JOIN 上使用 AND:

SELECT customer.users_id AS CustID, COUNT(VisitID) AS Jobs, COUNT(DISTINCT     VetID) AS Resources 
FROM customer 
LEFT JOIN visit ON customer.users_id = visit.CustID 
AND visit.Status NOT IN('Cancelled','ReScheduled')
GROUP BY customer.users_id