mysql 按日期分组

SELECT sale_date, SUM(amount) AS total_amount
FROM sales
GROUP BY sale_date
ORDER BY sale_date;

SELECT DATE(sale_date) AS sale_date, SUM(amount) AS total_amount
FROM sales
GROUP BY DATE(sale_date)
ORDER BY sale_date;

WITH date_range AS (
    SELECT DATE_ADD('2023-01-01', INTERVAL n DAY) AS date
    FROM (
        SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    ) t1
    CROSS JOIN (
        SELECT 0 n UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40 UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
    ) t2
)
SELECT dr.date, COALESCE(SUM(s.amount), 0) AS total_amount
FROM date_range dr
LEFT JOIN sales s ON DATE(s.sale_date) = dr.date
GROUP BY dr.date
ORDER BY dr.date;