我有以下情况。一个地区有多个地区，一个地区有多个地址，一个月内要多次访问一个地址。现在，我想生成有关某个区域的每月报告。 （已访问区域的次数）。我编写了查询，但由于未访问某些地址，结果集产生的区域更少。我有以下结构
桌子
区域：id|name（180行）//名称是唯一的
地区：id|name|area_id（1k行）
地址：id|name|territory_id（80k行）
Visiting_addresses：id|address_id|date|status（1M +行）//状态=> 1 =已访问，2 =待处理
我的查询如下。
select ar.id as area_id, ar.name as area,
sum(case when va.status = 1 then 1 else 0 end) as visited,
sum(case when va.status = 2 then 1 else 0 end) as pending,
count(va.id) as total
from areas ar
left join territories t on t.area_id=ar.id
left join addresses a on a.territory_id=t.id
left join visiting_addresses va on va.address_id=a.id
where month(va.date) = '01'
and year(va.date)='2020'
group by ar.id
面积表包含180个区域，但结果集仅显示144个区域。我的错误在哪里，对此有何解释？这些区域丢失了，因为他们没有来访。
最佳答案
您的WHERE子句将带有visiting_addresses的LEFT JOIN转换为INNER JOIN。由于它是LEFT-JOIN链中最右边的表，因此所有联接都将转换为INNER JOINS。为避免这种情况，您应该将相应的条件从WHERE子句移到ON子句：
select ar.id as area_id, ar.name as area,
sum(case when va.status = 1 then 1 else 0 end) as visited,
sum(case when va.status = 2 then 1 else 0 end) as pending,
count(va.id) as total
from areas ar
left join territories t on t.area_id=ar.id
left join addresses a on a.territory_id=t.id
left join visiting_addresses va
on va.address_id=a.id
and month(va.date) = '01'
and year(va.date)='2020'
group by ar.id
但是由于您有很多行，所以我宁愿运行两个查询。首先使用内部联接仅获得上个月形成地址的区域。但是，您应该更改
va.date的条件以利用索引：select ar.id as area_id, ar.name as area,
sum(case when va.status = 1 then 1 else 0 end) as visited,
sum(case when va.status = 2 then 1 else 0 end) as pending,
count(va.id) as total
from areas ar
join territories t on t.area_id=ar.id
join addresses a on a.territory_id=t.id
join visiting_addresses va on va.address_id=a.id
where va.date >= '2020-01-01'
and va.date < '2020-02-01'
group by ar.id
确保在
visiting_addresses(date)上具有索引，或者在visiting_addresses(date, address_id, status)上具有更好的索引。然后简单地得到所有区域
select ar.id as area_id, ar.name as area
from areas ar
并将
visited，pending和total设置为零（在应用程序代码中），并在第一个结果中添加缺失的区域。INNER JOIN应该更快，因为现在引擎可以开始使用WHERE条件的索引从
visiting_addresses读取必需的行。您还可以使用更复杂但单个的查询。想法是将LEFT JOIN与预聚合的子查询一起使用：
select ar.id as area_id, ar.name as area,
coalesce(visited, 0) as visited,
coalesce(pending, 0) as pending,
coalesce(total, 0) as total
from areas ar
left join (
select t.area_id
sum(case when va.status = 1 then 1 else 0 end) as visited,
sum(case when va.status = 2 then 1 else 0 end) as pending,
count(va.id) as total
from territories t
join addresses a on a.territory_id=t.id
join visiting_addresses va on va.address_id=a.id
where va.date >= '2020-01-01'
and va.date < '2020-02-01'
group by t.area_id
) x on x.area_id = ar.id
关于mysql - mysql多个左连接并按主表分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59857907/