mysql - 笛卡尔连接不匹配的行

Question

当 LEFT 连接不匹配时，我无法使我的查询工作。我认为这与笛卡尔连接有关。这是一个有效的查询:

select i.id, z.WeekNum, 0, count(s.id) from my156rowtable as i
left JOIN (select YEARWEEK( @startDate ) WeekNum, @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
from 
(select @startDate := '2016-04-20') sqlv,
my156rowtable
limit 156 ) z
on 1=1
where i.id = 2 and s.id = 0
group by z.WeekNum, s.id

这带回了:

|             2 |  201836 | 0 |        1 |
|             2 |  201837 | 0 |        1 |
|             2 |  201838 | 0 |        1 |
|             2 |  201839 | 0 |        1 |
|             2 |  201840 | 0 |        1 |
|             2 |  201841 | 0 |        1 |

但是随着我添加 stats_to_my156rowtable，我得到了 201838 和 201839 行。

select i.id, z.WeekNum, 0, count(s.id) from my156rowtable as i
left JOIN (select YEARWEEK( @startDate ) WeekNum, @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
from 
(select @startDate := '2016-04-20') sqlv,
my156rowtable
limit 156 ) z
on 1=1
left join stats_to_my156rowtable as s
on i.id = s.sid and z.WeekNum = YearWeek(s.date)
where i.id = 2 and s.id = 0
group by z.WeekNum, s.id

返回:

|             2 |  201836 | 0 |                  8 |
|             2 |  201837 | 0 |                 14 |
|             2 |  201840 | 0 |                  9 |

如果没有匹配项，我希望 count 为 0。我想我以后可以用 case 或 coalesce 来做到这一点，但我没有找到太多运气。

Answer 1

WHERE 子句中右表的条件将您的 LEFT JOIN 变为 INNER JOIN。基本上，它会过滤掉不具有 id = 0 的行，包括您的 NULL 行。将条件移动到 ON 子句可能会解决您的问题。

select i.id, z.WeekNum, 0, count(s.id) 
from my156rowtable as i
left JOIN (
    select YEARWEEK( @startDate ) WeekNum, @startDate := date_add( @startDate, interval 1 week ) EndOfWeek
    from (
        select @startDate := '2016-04-20'
    ) sqlv, my156rowtable
    limit 156 
) z on 1=1
left join stats_to_my156rowtable as s on i.id = s.sid and z.WeekNum = YearWeek(s.date) AND s.id = 0
where i.id = 2
group by z.WeekNum, s.id