mysql查询报表数据补全12个月
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
