mysql - 如何通过在mysql中过滤另一个表来列出表中的所有项目

Question

我有 2 张 table

表 1 = Visitacli

codcli-char(3)
nomecli-varchar(100)
------------------------
001 - Joao
002 - Maria
003 - Joana

表 2 = 访问

codcli-char(3)
datavisita-char(8)
quantidade-integer
-------------------------
001 - 20170810 - 2
001 - 20170811 - 3
001 - 20170815 - 5
002 - 20170812 - 1
002 - 20170816 - 9

我的选择

    SELECT
    visitacli.codcli,
    visitacli.nomecli,
    Sum(visitaate.quantidade) AS totalvisitas
FROM
    visitacli
LEFT JOIN visitaate ON visitacli.codcli = visitaate.codcli
WHERE
    visitaate.datavisita >= '20170801'
AND visitaate.datavisita <= '20170817'
GROUP BY
    visitacli.codcli,
    visitacli.nomecli

结果:

001 - Joao - 10
002 - Maria - 10

但是...没有列出003 - Joana。如何将表一的所有项目与表二的总计列出？

Answer 1

您必须移动 WHERE 子句的谓词:

WHERE
    visitaate.datavisita >= '20170801'
AND visitaate.datavisita <= '20170817'

到

LEFT JOIN visitaate 
   ON visitacli.codcli = visitaate.codcli
      AND visitaate.datavisita >= '20170801'
      AND visitaate.datavisita <= '20170817'