mysql最近7天销售额_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

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

本文地址：https://blog.csdn.net/qq_41937388/article/details/107391926

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！