mysql查询报表数据补全12个月-526互联

mysql中生成多行序列

1 SELECT
2     SUBSTRING_INDEX(
3         SUBSTRING_INDEX('1,2,3,4,5,6,7,8,9,10,11,12',',',help_topic_id + 1),',' ,- 1) ROWNUM
4 FROM
5     mysql.help_topic
6 WHERE
7     help_topic_id < (LENGTH('1,2,3,4,5,6,7,8,9,10,11,12')) - LENGTH(REPLACE ('1,2,3,4,5,6,7,8,9,10,11,12', ',', '')) + 1;

补全12个月，之后再join业务数据就可以补全没有数据的月份

 1 select 
 2     DATE_FORMAT(DATE_ADD(DATE_ADD(now(), INTERVAL '-1' YEAR), INTERVAL ROWNUM month),'%m') 月,
 3     DATE_FORMAT(DATE_ADD(DATE_ADD(now(), INTERVAL '-1' YEAR), INTERVAL ROWNUM month),'%Y%m') 年月
 4 from(
 5 SELECT
 6     SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6,7,8,9,10,11,12',',',help_topic_id + 1),',' ,- 1) ROWNUM
 7 FROM
 8     mysql.help_topic
 9 WHERE
10     help_topic_id < (LENGTH('1,2,3,4,5,6,7,8,9,10,11,12')) - LENGTH(REPLACE ('1,2,3,4,5,6,7,8,9,10,11,12', ',', '')) + 1
11 ) t