mysql统计一周内每天的数据统计

WITH RECURSIVE dates AS (
  SELECT CURDATE() - INTERVAL 6 DAY AS date  -- 7天前
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM dates WHERE date < CURDATE()  -- 递归到当天
)
-- 左连接业务表统计数据
SELECT 
  a.date,
  COUNT(b.aid) AS count  -- 统计订单数
FROM 
  dates a
LEFT JOIN 
  video_alarm b
  ON a.date = DATE(b.res_time)  -- 按日期匹配（忽略时分秒）
WHERE 
  a.date >= CURDATE() - INTERVAL 6 DAY  -- 确保只取过去7天
GROUP BY 
  a.date
ORDER BY 
  a.date ASC;