Mysql：获取距今最近12个月的数据

获取最近12个月的数据 并将没有数据的月份置为0

SELECT IFNULL(b.value,0) AS VALUE,a.name   /*将value初始化为0  并将其作为value列下的数据*/
 FROM(
 /*过去12个月的日期格式*/
	SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `name` /*UNION 操作符用于合并两个或多个 SELECT 语句的结果集。*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `name`   /* 其中为单个数据集*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `name`   /*curdate（）表示为当前日期 相当与  now（）*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `name`   /* interval表示时间间隔 有多久*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `name`   /* DATE_SUB() 函数从日期减去指定的时间间隔。*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `name`    /* date_format 表示格式化时间格式*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `name`   /* as 新定义一个列表 并将 获得的数据放到 该列表中*/
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `name`  /* left join 左连接 ？？？？？？？？  不知 */
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `name`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `name`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `name`
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `name`) a 
    LEFT JOIN
    /*  将 count（*）作为 value*/     /*group by 没有它 会报错   意思 不详4*/
(SELECT   COUNT(*) AS VALUE,DATE_FORMAT(TIME,'%Y-%m') AS formate_time FROM showdata  GROUP BY formate_time) AS b   
ON a.name = b.formate_time    
    

DATE_SUB() 函数从日期减去指定的时间间隔。
eg：

SELECT COUNT(DATE_FORMAT(TIME,'%Y-%m')) AS VALUE,DATE_FORMAT(TIME,'%Y-%m') AS NAME FROM showdata WHERE TIME BETWEEN DATE_SUB(NOW(),INTERVAL 12 MONTH) AND NOW() GROUP BY DATE_FORMAT(TIME, '%Y-%m');

获取12个月的数据 若没有数据 不置为0
只显示有数据的 月份