mysql - mySQL左连接，右表具有(无行)条件

SHOPS
+----+------------+
| id | shop_name  |
+----+------------+

ORDERBOOKINGS
+-----+---------+-------------+------------+
| id  | shop_id | grand_total | created_at |
+-----+---------+-------------+------------+

我想要一张这样的桌子：

+------------+--------------+--------------+
| shop_name  | total_orders | total_amount |
+------------+--------------+--------------+

条件是我有日期过滤器，该过滤器仅返回指定日期之间的订单总数。我希望它返回所有商店（如果在这些日期之间没有某些商店的订单，那么它应该返回那些total_orders为0的商店）。

注意：有些商店甚至可能在订单表中没有条目。

我尝试了以下操作，但是它无法从shops表返回所有行：

SELECT COUNT(orderbookings.id),
       SUM(orderbookings.grand_total),
       shops.shop_name
FROM `orderbookings`
LEFT JOIN shops
    on orderbookings.shop_id = shops.id
where orderbookings.created_at BETWEEN "2015-10-22 17:02:02" AND "2017-03-07 17:02:02"
GROUP BY shops.id

知道我该如何实现吗？

谢谢。

最佳答案

在查询中将where替换为and，将LEFT JOIN替换为RIGHT JOIN：

SELECT
    COUNT(orderbookings.id),
    COALESCE(SUM(orderbookings.grand_total), 0),
    shops.shop_name
FROM `orderbookings`
RIHGT JOIN shops on orderbookings.shop_id = shops.id and orderbookings.created_at BETWEEN "2015-10-22 17:02:02" AND "2017-03-07 17:02:02"
GROUP BY shops.id

说明：
1）如果要获得所有商店，则应使用shops作为主表，然后使用左联接orderbookings，这里我使用右联接，因为您将orderbookings用作主表；
2）如果使用orderbookings中的where列，则左联接将作为内部联接。

最后，left join解决方案将如下所示：

SELECT
    COUNT(orderbookings.id),
    COALESCE(SUM(orderbookings.grand_total), 0),
    shops.shop_name
FROM `shops `
LEFT JOIN orderbookings on orderbookings.shop_id = shops.id and orderbookings.created_at BETWEEN "2015-10-22 17:02:02" AND "2017-03-07 17:02:02"
GROUP BY shops.id

关于mysql - mySQL左连接，右表具有(无行)条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42664175/