1.mysql 自动生成日期并且查询时候如果该日期没有值那就为0
表示为进行了左连接查询如果有值就附上,没有的话就赋0.
SET @days = TIMESTAMPDIFF( DAY, DATE_ADD( CURDATE(), INTERVAL - 1 MONTH ), CURDATE() )+0;
SET @number = 1;
SELECT DATE_FORMAT( r.datameths, '%m.%d')AS 'dataTime', IFNULL(o.countnumber,'0') AS 'countNumber'
FROM
(
SELECT
t.datameth AS 'datameths'
FROM
(
SELECT
@number := @number - 1 AS 'numberbvalue',
ADDDATE( CURDATE(), @number ) AS 'datameth'
FROM
(
SELECT
a
FROM
( SELECT '1' AS a UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS a
JOIN ( SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5' UNION SELECT '6' ) AS b ON 1
) e
WHERE
@number < @days ) t WHERE numberbvalue > 0- @days ) r
LEFT JOIN (time
SELECT datatime as 'datatime ',countnumber as '',countnumber ' FROM 表
)AS o
ON r.datameths=o.datatime;