WITH RECURSIVE daily_dates AS (
SELECT
date_format( '2024-03-01', '%Y-%m-%d' ) AS date_in_month UNION ALL
SELECT
DATE_ADD( date_in_month, INTERVAL 1 DAY )
FROM
daily_dates
WHERE
DATE_ADD( date_in_month, INTERVAL 1 DAY ) <= date_format( '2024-03-31', '%Y-%m-%d' )
) SELECT REPLACE
( DATE_FORMAT( daily_dates.date_in_month, '%e' ), '', '0' ) AS recordDate,
COALESCE ( COUNT( dt.id ), 0 ) AS totalCount
FROM
daily_dates
LEFT JOIN you_table AS dt ON daily_dates.date_in_month = DATE ( dt.ticket_create_time )
GROUP BY
daily_dates.date_in_month
ORDER BY
daily_dates.date_in_month;
mysql 月内每天的数据统计 mysql 处理日期格式 2024-03-01 为 1 或2024-03-11 为11
最新推荐文章于 2024-07-16 17:34:10 发布