使用一个日期生成表或子查询来生成当月的所有日期,并将其与原始查询结果进行左连接
比如:
WITH RECURSIVE date_series AS (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS day
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY)
FROM date_series
WHERE day < LAST_DAY(CURDATE())
)
SELECT
LPAD(DAY(ds.day), 2, '0') AS day,
COALESCE(COUNT(uf.xm), 0) AS xm_count
FROM
date_series ds
LEFT JOIN
uf_ryryxx uf ON DATE(uf.rcsj) = ds.day
WHERE
ds.day >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
GROUP BY
ds.day
ORDER BY
ds.day;