输出当月每天的数据，当天没有数据为0 mysql

假设你有一个名为 `table` 的表，其中包含一个 `date` 字段表示日期，以及其他数据字段，你可以使用以下 SQL 语句输出当月每天的数据，当天没有数据为 0：

SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date, COALESCE(SUM(data), 0) AS data
FROM (
  SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date, data
  FROM table
  WHERE date BETWEEN DATE_FORMAT(NOW(), '%Y-%m-01') AND LAST_DAY(NOW())
) AS t
RIGHT JOIN (
  SELECT DATE_FORMAT(date, '%Y-%m-%d') AS date
  FROM (
    SELECT DATE_FORMAT(DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL (tens+units) DAY), '%Y-%m-%d') AS date
    FROM (
      SELECT 0 AS tens 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
    ) AS t1
    CROSS JOIN (
      SELECT 0 AS units 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
    ) AS t2
    WHERE DATE_ADD(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL (tens+units) DAY) <= LAST_DAY(NOW())
  ) AS days
) AS days
ON t.date = days.date
GROUP BY days.date
ORDER BY days.date;

该 SQL 语句使用了一个右连接（RIGHT JOIN）来保证输出当月所有天数的数据，即使当天没有数据也要输出 0。其中，子查询 `days` 生成了当前月份的所有日期，子查询 `t` 获取了当前月份内的所有数据，最后使用 `GROUP BY` 和 `SUM` 函数对数据进行聚合，生成当月每天的数据。