查询指定用户的所有店铺并关联指定日期范围的订单

要解决这个问题，核心是保留用户名下所有店铺，同时统计指定时间段内的订单数（无订单时显示0），关键在于使用LEFT JOIN并正确放置日期过滤条件：

正确SQL语句 #

SELECT 
    s.id AS shop_id,
    s.shop_name,
    u.name AS first_name,
    COUNT(o.id) AS order_count
FROM shops s
JOIN users u ON u.id = s.user_id
LEFT JOIN orders o 
    ON o.shop_id = s.id 
    AND o.created_at >= '指定开始日期 00:00:00' 
    AND o.created_at <= '指定结束日期 23:59:59'
WHERE s.user_id = 123 -- 替换为目标用户ID
GROUP BY s.id, s.shop_name, u.name;

关键说明 #

1. LEFT JOIN 关联订单表：
   使用LEFT JOIN orders而非普通JOIN，确保即使店铺在指定时间段没有订单，也会被保留在结果集中，不会被过滤掉。

2. 日期条件放在JOIN的ON子句：
   如果把日期过滤条件放在WHERE子句中，会导致LEFT JOIN退化为INNER JOIN的效果——没有匹配订单的店铺会被过滤掉。将日期条件写在ON里，只会筛选出符合时间范围的订单，不影响店铺本身的显示。

3. COUNT(o.id) 统计订单数：
   对于无订单的店铺，o.id会是NULL，而COUNT()函数不会统计NULL值，因此会自动返回0，无需额外处理。

测试验证 #

• 当指定日期为2024-10-11时，orders表无匹配记录，结果中order_count为0；
• 当指定日期为2024-10-10时，orders表有1条匹配记录，order_count为1，完全符合预期结果。

内容的提问来源于stack exchange，提问作者Josh Bolton