MYSQL 查询不返回与连接查询无关的内容

Question

我编写了一个查询来获取表“User”中的所有数据以及表“Service”中相应的“Transaction”计数。我的表 1 有 100 个用户。但目前只有2个用户与服务表有关系。现在我的查询只返回两个结果。但我使用左外查询来获取与服务表无关的所有用户。

这是查询:

SELECT users.*, 
count(service.recipient_number) as total_transaction FROM `users`
LEFT OUTER join service ON users.id = service.office_id
WHERE (users.del_status = 0 and service.del_status = 0 and 
users.type="agency") 
group BY users.office_name 

当前输出:

 Username  total_transaction
+++++++++++++++++++++++++++++++++++++
   Dhaka         2
   Ctg           1

期望输出:

  Username  total_transaction
  +++++++++++++++++++++++++++++++++++++
   Dhaka         2
   Ctg           1
   Sylhet        0
   Comilla       0
   Mym           0

Answer 1

SELECT users.office_name,
       count(service.recipient_number) as total_transaction 
FROM `users`
LEFT OUTER JOIN service ON users.id = service.office_id
                        AND service.del_status = 0
                        AND year(curdate()) = year(service.creation_time) 
                        AND month(curdate()) = month(service.creation_time)
WHERE users.del_status = 0  
AND users.type = 'agency'
GROUP BY users.office_name 

您需要将有关连接表的 where 条件直接放入 join 中。

为什么？因为 where子句过滤了完整的结果集。但 join 条件仅过滤连接的表结果。