mysql查询近七天、近三十天、近年（按月份）的所有统计数据

目录

一、查询近七天、近三十天

二、查询近年，即十二个月的数据

---

在做统计表的时候，需要用mysql查询近7天的数据，当某一天数据为0的时候也需要返回

主要用到：

• infull函数
• union
• interval函数
• date_sub函数

infull函数：infull() 函数用于判断第一个表达式是否为 null，如果为 null 则返回第二个参数的值，如果不为 null 则返回第一个参数的值。 

union：union操作符用于合并两个或多个 select 语句的结果集。

interval函数：interval()函数将N个列表(N1，N2，N3，等等)的值进行比较。该函数返回0如果N<N1，1如果N<N2，2如果N<N 3，依此类推。它会返回1，如果N为NULL。值列表必须是N1<N2<N3形式才能正常工作 

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

一、查询近七天、近三十天

SELECT a.item,IFNULL(b.value,0) AS value
FROM (
    SELECT CURDATE() AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS item
    UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS item
) a LEFT JOIN (
  SELECT DATE(create_time) AS date, count(date_format(create_time,'%Y-%m-%d')) AS value
  FROM b_statistic
  GROUP BY DATE(create_time)
) b ON a.item = b.date;

 

近三十天可以给加到 SELECT DATE_SUB(CURDATE(), INTERVAL 29 DAY) AS item 

二、查询近年，即十二个月的数据

SELECT
	tab.month as item,
	ifnull(va.value, 0) as  value
FROM
	(SELECT
	date_format( date_sub( curdate( ), INTERVAL t.count MONTH ), '%Y-%m' ) AS MONTH 
FROM
	(
	SELECT
		t.c AS count 
	FROM
		(
		SELECT
			0 AS c UNION
		SELECT
			1 AS c UNION
		SELECT
			2 AS c UNION
		SELECT
			3 AS c UNION
		SELECT
			4 AS c UNION
		SELECT
			5 AS c UNION
		SELECT
			6 AS c UNION
		SELECT
			7 AS c UNION
		SELECT
			8 AS c UNION
		SELECT
			9 AS c UNION
		SELECT
			10 AS c UNION
		SELECT
			11 AS c 
		) t 
	) AS t) tab
LEFT JOIN (
	SELECT
		DATE_FORMAT(b.create_time, '%Y-%m') as item,
		count(DATE_FORMAT(b.create_time, '%Y-%m')) as value
	FROM
		b_statistic b
	WHERE
		DATE_FORMAT(b.create_time, '%Y-%m') > DATE_FORMAT(
			date_sub(curdate(), INTERVAL 12 MONTH),
			'%Y-%m'
		)
	GROUP BY
		item
) va ON tab.month = va.item