SQL按日期分组,但也得到没有记录的日期

WITH date_range AS (
  SELECT DATE(NOW()) - INTERVAL (a.N + (10 * b.N) + (100 * c.N) + (1000 * d.N)) DAY AS date
  FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) a
  CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) b
  CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) c
  CROSS JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) d
),
orders_with_dates AS (
  SELECT date_range.date, SUM(orders.order_amount) AS total_amount
  FROM date_range
  LEFT JOIN orders ON DATE(orders.order_date) = date_range.date
  GROUP BY date_range.date
)
SELECT orders_with_dates.date, orders_with_dates.total_amount
FROM orders_with_dates
WHERE orders_with_dates.date BETWEEN DATE(NOW()) - INTERVAL 30 DAY AND DATE(NOW())
ORDER BY orders_with_dates.date ASC;