SQL中如何根据Store的Weekly/Yearly类型统计订单数量及总量？

问题背景 #

现有两个数据表：

Stores表 #

Orders表 #

统计需求 #

当传入周日期范围（2022-12-26 ~ 2023-01-01）时，需按以下规则统计各门店的订单数和总数量：

• 若门店类型为Yearly：统计该门店全年（与传入周范围同一年）的所有订单
• 若门店类型为Weekly：统计该门店在传入周范围内的订单

预期结果：

尝试的错误代码 #

SELECT s.Id,
       s.Name,
       COUNT(o.Id) AS 'Count of orders',
       sum(o.Qty) AS 'Total Qty'
  FROM Stores AS s
  LEFT JOIN Orders AS o
    ON o.StoreId = s.id
   AND (OrderDate >= '2022-12-26' AND OrderDate <= '2023-01-01')
 GROUP BY s.Id, OrderDate
 ORDER BY OrderDate DESC

问题分析 #

这段代码存在两个核心问题：

1. JOIN条件过于局限：仅过滤了周范围的订单，完全没处理Yearly门店需要统计全年订单的逻辑
2. 分组错误：GROUP BY中包含了OrderDate，会导致每个订单日期单独生成一行，无法按门店进行汇总

正确解决方案 #

通过在聚合函数中使用CASE语句，针对不同类型的门店设置不同的统计条件：

SELECT 
    s.Id,
    s.Name,
    -- 统计符合条件的订单数：不符合条件的返回NULL，不会被COUNT计数
    COUNT(CASE 
        WHEN s.Type = 'Weekly' AND o.OrderDate BETWEEN '2022-12-26' AND '2023-01-01' THEN o.Id
        WHEN s.Type = 'Yearly' AND YEAR(o.OrderDate) = YEAR('2022-12-26') THEN o.Id
        ELSE NULL 
    END) AS 'Count of orders',
    -- 统计符合条件的总数量：不符合条件的返回0，不影响SUM结果
    SUM(CASE 
        WHEN s.Type = 'Weekly' AND o.OrderDate BETWEEN '2022-12-26' AND '2023-01-01' THEN o.Qty
        WHEN s.Type = 'Yearly' AND YEAR(o.OrderDate) = YEAR('2022-12-26') THEN o.Qty
        ELSE 0 
    END) AS 'Total Qty'
FROM Stores AS s
LEFT JOIN Orders AS o ON o.StoreId = s.Id
GROUP BY s.Id, s.Name
ORDER BY s.Id;

代码说明 #

• CASE分支逻辑：分别对Weekly和Yearly门店设置统计条件，Weekly判断订单是否在指定周范围内，Yearly判断订单年份是否与传入周范围的年份一致
• LEFT JOIN：保证即使没有符合条件的订单（如Store4），也会在结果中显示，避免遗漏门店
• 聚合函数处理：COUNT会忽略NULL值，SUM会将0纳入计算，确保无符合条件订单时显示0
• 分组规则：仅按门店的Id和Name分组，保证每个门店只返回一行统计结果

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