mysql - 城镇报告(订单数)

Question

我正在创建一个按城镇排序的报告。

SELECT S.city, count(*) as NumOfOrders
    FROM Shop as S
    LEFT JOIN orders O ON O.ShopID = S.ShopID
WHERE O.status = 4
Group by S.city

结果显示如下:

Town 1  |   53
Town 2  |   45
Town 3  |   64

它工作正常，但即使没有订单我也想显示所有城镇？

预期结果:

Town 1  |   53
Town 2  |   45
Town 3  |   64
Town 4  |   0
Town 5  |   0

我曾尝试将 LEFT JOIN 替换为 RIGHT JOIN，但这甚至不起作用。相同的结果。

Answer 1

您的过滤器排除了没有订单的那些。尝试将条件放入连接中，您应该没问题。

SELECT S.city, count(O.any_non_nullable_field) as NumOfOrders
    FROM Shop as S
    LEFT JOIN orders O ON (O.ShopID = S.ShopID AND O.status = 4)
Group by S.city