使用mysql 实现统计近一个年的数据和一个月的数据 

 1 SELECT a.allDate,
 2  IFNULL(b.num,0) AS num
 3  FROM 
 4 (SELECT 
 5  @s:=@s+1 AS num,
 6 DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL @s MONTH),'%Y-%m') AS allDate
 7 FROM 
 8   information_schema.`TABLES`,
 9   (SELECT @s:=-1) temp
10   WHERE 
11   @s<12
12   ORDER BY allDate
13  )a 
14  LEFT JOIN (
15            SELECT COUNT(*) AS num,
16            DATE_FORMAT(pdate,'%Y-%m') AS pdate
17            FROM 
18            product 
19            WHERE 
20            DATE_SUB(CURDATE(),INTERVAL 1 YEAR)<=pdate
21            GROUP BY 
22            DATE_FORMAT(pdate,'%Y-%m')
23  
24  )  b ON a.allDate=b.pdate
25  ORDER BY a.allDate

近一年可以设置条件为12，如果设置近一个月的只需要将month换成day，将条件设置成为30

posted @ 2020-12-29 16:33  Shy哥  阅读(463)  评论(0)    收藏  举报