MySQL的左用（没有行）连接条件上右表

Question

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

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

我希望得到像这样的表：

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

的情况是，我有日期过滤器指定之间只返回订单总额日期。我希望它返回所有商店（如果在这些日期之间没有一些商店的订单，那么它应该将total_orders作为0返回）。

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

我曾尝试以下，但它无法从商店返回表中的所有行：

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 

任何想法，我怎么能做到这一点？

谢谢。

Answer 1

更换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）如果在where中使用orderbookings的列，则左连接将作为内连接。

最后，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 

Answer 2

你想扭转你的加入，并添加一些IFNULLs：

SELECT IFNULL(COUNT(orderbookings.id),0), 
     IFNULL(SUM(orderbookings.grand_total),0), 
     shops.shop_name 
FROM `orderbookings` 
RIGHT OUTER 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