我有一个执行 2 个 LEFT JOIN 和 1 个 GROUP BY 的查询，但缺少结果：

Question

以下查询应返回大约 1159 个结果，但只返回大约 880 个结果； 如果我只加入“c”宽度“mm”，我仍然会得到 1159 个结果。只有当所有 3 个表都得到 880 个结果时。我也使用了但没有运气：RIGHT JOIN、RIGHT OUTER JOIN、LEFT OUTER JOIN、INNER JOIN。我尝试在 JOIN 语法中使用“()”和不使用“()”，但也没有运气。

任何帮助表示赞赏

SELECT c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi,

Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END),

Sum(CASE WHEN m.mcdmv=2 THEN m.mqtd END),

Sum(CASE WHEN m.mcdmv=13 THEN m.mqtd END),
Sum(CASE WHEN m.mcdmv=14 THEN m.mqtd END),

MAX(CASE WHEN m.mcdmv=82 THEN m.mdata END)



FROM 
(zzz.ccc c 
LEFT JOIN zzz.mmmm mm ON c.crnp = mm.arncd)
LEFT JOIN zzz.mm m ON m.mrncd = c.crnp



WHERE 
((c.cetcm='xcd') OR
(c.cetcm='ewfwe') OR
(c.cetcm='fewfew') OR
(c.cetcm='fewf')) AND

(m.MDATA Between 20100101 And 20110406) AND

((m.mcdmv=11) OR 
(m.mcdmv=12) OR 
(m.mcdmv=13) OR 
(m.mcdmv=14)) AND

(mm.aarm=1)


GROUP BY c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi

ORDER BY c.ccod

Answer 1

正如所建议的，您需要将左联接中针对表的条件移至各自的 ON 子句中：

SELECT c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi
    , Sum(CASE WHEN m.mcdmv=11 THEN m.mqtd END) As Total11
    , Sum(CASE WHEN m.mcdmv=12 THEN m.mqtd END) As Total12
    , Sum(CASE WHEN m.mcdmv=13 THEN m.mqtd END) As Total13
    , Sum(CASE WHEN m.mcdmv=14 THEN m.mqtd END) As Total14
    , MAX(CASE WHEN m.mcdmv=82 THEN m.mdata END) As Max82
FROM (zzz.ccc c 
    LEFT JOIN zzz.mmmm mm 
        ON (c.crnp = mm.arncd
        And m.aarm = 1))
    LEFT JOIN zzz.mm m 
        ON (m.mrncd = c.crnp
            And m.mcdmv In(11,12,13,14,82)
            And m.MDATA Between #20100101# And #20110406#)
WHERE c.cetcm In('xcd','ewfwe','fewfew','fewf')
GROUP BY c.ccod, c.cetcm, c.cdscr, c.cpcul, c.cdent, mm.amexi
ORDER BY c.ccod

此外，我还使用了 IN 函数，它可以让您传递一系列值。顺便说一句，Access 也可能会因为没有列聚合函数的列别名而犹豫不决。在这里我只是使用了一些简单的东西。