我正在尝试获取每个商店的所有累计销售额，即使该值为null（条件无顺序），但LEFT JOIN仅给我提供具有对应关系的行，这不适合我：
SELECT s.identifierExt as StoreID,
YEAR(o.creation) AS Year,
MONTHNAME(o.creation) AS Month,
MONTH(o.creation) AS IDMonth,
ROUND(SUM(o.price), 2) AS Sales
FROM store s
LEFT JOIN order o ON o.store = s.id
AND (o.creation < '2018-09-13 00:00:00')
AND (o.place NOT IN ('PENDING','CANCELLED'))
AND (o.creation > '2018-01-12 00:00:00')
GROUP BY Year, Month, StoreID
ORDER BY IDMonth, StoreID ASC
提前致谢。
最佳答案
可能是由于INNER和OUTER连接之间的差异。内部联接要求有对应的行（联接条件匹配），外部联接没有这样的要求：它将返回带有NULL的列。
似乎您想加入OUTER：
SELECT s.identifierExt as StoreID,
YEAR(o.creation) AS Year,
MONTHNAME(o.creation) AS Month,
MONTH(o.creation) AS IDMonth,
ROUND(SUM(o.price), 2) AS Sales
FROM store s
LEFT OUTER JOIN order o ON o.store = s.id
AND (o.creation < '2018-09-13 00:00:00')
AND (o.place NOT IN ('PENDING','CANCELLED'))
AND (o.creation > '2018-01-12 00:00:00')
GROUP BY Year, Month, StoreID
ORDER BY IDMonth, StoreID ASC
这样，您还将获得没有订单的商店。
另请参见this answer on the difference between inner and outer joins
关于mysql - 使用GROUP BY的LEFT JOIN不返回预期的LEFT JOIN结果，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55703237/