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

我有2张桌子

表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。
如何列出表一的所有项目和表二的总数？

最佳答案

您必须移动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'