mysql 组装当年所有月份及按需组装某个时间段的每天

因为要做统计图表功能，需要显示当年所有年份数据，还未到的年份数据要补0 ，所以要组装当年所有年份的SQL，找到如下参照。

图表

SELECT

CASE

WHEN

      length( mon ) =1 THEN

        concat(LEFT ( CURRENT_DATE,5 ),'0', mon )ELSE concat(LEFT ( CURRENT_DATE,5 ), mon )

END months

FROM

  (SELECT @m := @m +1 mon FROM mysql.help_topic, (SELECT @m :=0 ) a ) t

LIMIT 12;

---

获取某个月的所有天数

SELECT

    @cdate := date_add(@cdate, interval - 1 day) as date_str

FROM(SELECT @cdate := date_add(LAST_DAY( '2019-09-01' ), interval + 1 day) from mysql.help_topic ) t

where @cdate > '2019-09-01';

---

另一种方式：

SELECT ADDDATE(y.first, x.d - 1) as d

FROM

(

SELECT 1 AS d UNION ALL

SELECT 2 UNION ALL

SELECT 3 UNION ALL

SELECT 4 UNION ALL

SELECT 5 UNION ALL

SELECT 6 UNION ALL

SELECT 7 UNION ALL

SELECT 8 UNION ALL

SELECT 9 UNION ALL

SELECT 10 UNION ALL

SELECT 11 UNION ALL

SELECT 12 UNION ALL

SELECT 13 UNION ALL

SELECT 14 UNION ALL

SELECT 15 UNION ALL

SELECT 16 UNION ALL

SELECT 17 UNION ALL

SELECT 18 UNION ALL

SELECT 19 UNION ALL

SELECT 20 UNION ALL

SELECT 21 UNION ALL

SELECT 22 UNION ALL

SELECT 23 UNION ALL

SELECT 24 UNION ALL

SELECT 25 UNION ALL

SELECT 26 UNION ALL

SELECT 27 UNION ALL

SELECT 28 UNION ALL

SELECT 29 UNION ALL

SELECT 30 UNION ALL

SELECT 31

) x,

(

SELECT '2018-02-05' - INTERVAL DAY('2018-02-05') - 1 DAY AS first,

DAY(LAST_DAY('2018-02-05')) AS last) y

WHERE x.d <= y.last