mysql - WHERE 子句导致表忽略 LEFT JOIN 规则

Question

我实际上是在尝试修改 this存储过程。

修改后的存储过程:

CREATE PROCEDURE sp1(d1 date, d2 date, client INT(10))
    declare d datetime;

    create TEMPORARY TABLE foo (d date NOT NULL, Amount INT(10) DEFAULT 0);

    set d = d1;

    while d <= d2 do 
        insert into foo (d) values (d);
        set d = date_add(d, interval 1 day);
    end while;

    SELECT SUM(p.Amount), foo.d
    FROM foo LEFT JOIN ItemTracker_dbo.Payment ON foo.d = p.Datetime
    WHERE p.ClientId = ClientId
    GROUP BY
        foo.d;

    DROP TEMPORARY TABLE foo;
end PROCEDURE

注意: WHERE 子句... p.ClientId = client

我绞尽脑汁想弄清楚为什么它会省略零。

删除 WHERE p.ClientId = client 过程开始返回 NULL...

为什么 WHERE 子句省略空行？我可能误解了究竟 LEFT JOIN 是什么。

如何过滤 SUM(p.Amount) 结果以仅返回总和 WHERE clientId = client？

Answer 1

首先，你的示例代码是不是

SELECT SUM(p.Amount), foo.d 
    FROM foo LEFT JOIN ItemTracker_dbo.Payment ON foo.d = p.Datetime 
    WHERE p.ClientId = ClientId 
    GROUP BY 
        foo.d; 

缺少 p 表 ItemTracker_dbo.Payment 的别名 ??即，不应该是:

SELECT SUM(p.Amount), foo.d 
    FROM foo 
       LEFT JOIN ItemTracker_dbo.Payment p
            ON foo.d = p.Datetime 
WHERE p.ClientId = ClientId 
GROUP BY foo.d; 

无论如何，你遇到这个问题的原因是 where 子句条件直到外连接被处理后才应用(来自外侧的行被添加回)所以你需要将 where 条件移动到加盟条件:

SELECT SUM(p.Amount), foo.d 
    FROM foo 
       LEFT JOIN ItemTracker_dbo.Payment p
            ON foo.d = p.Datetime 
                And p.ClientId = ClientId 
GROUP BY foo.d;