如何修改SQL实现本周订单统计及与上周订单数的差值计算

API 探路者

2026-8-16

订单统计SQL优化方案

下面是满足需求的SQL查询语句，既保留本周订单统计，又新增上周订单数与本周的差值计算：

方式一：嵌套子查询（与原SQL风格一致）

SELECT 
  p.name AS 产品,
  -- 统计本周订单总数
  (SELECT COUNT(o.product_id)
   FROM orders o
   WHERE o.product_id = p.id 
     AND DATE(o.order_date) >= [本周起始日期] 
     AND DATE(o.order_date) <= [本周结束日期]) AS 本周订单总数,
  -- 计算上周订单数与本周的差值
  (SELECT COUNT(o.product_id)
   FROM orders o
   WHERE o.product_id = p.id 
     AND DATE(o.order_date) >= DATE_SUB([本周起始日期], INTERVAL 7 DAY) 
     AND DATE(o.order_date) <= DATE_SUB([本周结束日期], INTERVAL 7 DAY)) 
  - 
  (SELECT COUNT(o.product_id)
   FROM orders o
   WHERE o.product_id = p.id 
     AND DATE(o.order_date) >= [本周起始日期] 
     AND DATE(o.order_date) <= [本周结束日期]) AS 上周与本周订单差值
FROM products p;

方式二：LEFT JOIN + 条件聚合（效率更高）

如果订单表数据量较大，推荐用这种方式，减少重复子查询的执行次数：

SELECT 
  p.name AS 产品,
  SUM(CASE WHEN DATE(o.order_date) BETWEEN [本周起始日期] AND [本周结束日期] THEN 1 ELSE 0 END) AS 本周订单总数,
  -- 先统计上周订单数，再减去本周订单数得到差值
  SUM(CASE WHEN DATE(o.order_date) BETWEEN DATE_SUB([本周起始日期], INTERVAL 7 DAY) AND DATE_SUB([本周结束日期], INTERVAL 7 DAY) THEN 1 ELSE 0 END) 
  - SUM(CASE WHEN DATE(o.order_date) BETWEEN [本周起始日期] AND [本周结束日期] THEN 1 ELSE 0 END) AS 上周与本周订单差值
FROM products p
LEFT JOIN orders o ON p.id = o.product_id
GROUP BY p.id, p.name;