mysql 查询无则为0_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