mysql查询当月每一天数据，如果当天无数据，则为0

 

　　　　　　　　查询当月1号至31号每天的数据，

SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY),
INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY
) DAY
FROM
mysql.help_topic
WHERE
help_topic_id < DAY ( last_day( curdate( ) ) )
ORDER BY
help_topic_id

 

 　　　　　　　　　　　　　　　　查询 1号至当前日期每天的数据

 

SELECT
date_add( date_sub( '2020-08-01 00:00:00.0', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) 'day'
FROM
mysql.help_topic
WHERE
help_topic_id < DATEDIFF( NOW( ), date_sub( '2020-08-01 00:00:00.0', INTERVAL 1 DAY ) )
ORDER BY
help_topic_id

 

 

SELECT DATE_FORMAT(leftdata.day,'%Y-%m-%d'),IFNULL(rightdata.countNumber,'0') FROM
(
SELECT
date_add(DATE_ADD(curdate(), INTERVAL - DAY(curdate()) + 2 DAY),
INTERVAL (cast( help_topic_id AS signed INTEGER ) - 1 ) DAY
) DAY
FROM
mysql.help_topic
WHERE
help_topic_id < DAY ( last_day( curdate( ) ) )
ORDER BY
help_topic_id
) as leftdata
LEFT JOIN
(
SELECT
DATE_FORMAT( create_time, '%Y-%m-%d' ) AS dateTime,
COUNT( 1 ) AS countNumber
FROM
t_mscm_sales
GROUP BY
DATE_FORMAT( create_time, '%Y-%m-%d' )
) as rightdata
ON
DATE_FORMAT(leftdata.day,'%Y-%m-%d') = rightdata.dateTime

 

 

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　----------------------------------------------------------------------------------------

查询当月1号至当前日期的所有数据，如果当天无数据则为0

SELECT
( CASE WHEN b.c_id IS NULL THEN 0 ELSE COUNT( * ) END ) count,
a.DAY
FROM
(
SELECT
date_add( date_sub( '2020-08-01', INTERVAL 1 DAY ), INTERVAL ( cast( help_topic_id AS signed INTEGER ) + 1 ) DAY ) DAY
FROM
mysql.help_topic
WHERE
help_topic_id < DATEDIFF( NOW( ), date_sub( '2020-08-01', INTERVAL 1 DAY ) )
ORDER BY
help_topic_id
) a
LEFT JOIN t_mscm_sales b ON DATE( b.update_time ) = a.DAY
AND
b.n_states = 6
GROUP BY
a.DAY