MYSQL-查询过去7天的数据当天没有值的赋予默认值0或者其他-CSDN博客

2019独角兽企业重金招聘Python工程师标准>>>

最近公司在做统计，前端使用了ECHART，需要有7天的数据结果，后台调度数据的话使每天晚上0点一刻跑昨天一天的数据汇总。但是如果使新的数据，就不会有7天的数据可查。所以怎么构建空的统计数据给前端呢。

由于本人sql能力水平真的很差（这段时间要多加强），在百度和google了一大会之后，发现了两个不错的给了我思路的blog：

1.http://www.cnblogs.com/jethypc/p/5065857.html

2.https://segmentfault.com/q/1010000006663965

在查询过去N天数据的时候，可以使用adddate()函数和 CROSS JOIN 查询。

1.创建基础表：

CREATE TABLE num (i INT);
INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

2.查询时间范围

SELECT
    adddate('2015-11-25', numlist.id) AS 'date'
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
    ) AS numlist
WHERE
    adddate('2015-11-25', numlist.id) <= '2015-12-25';

3 创建数据表

create table datatable(tDate char(10) PRIMARY KEY,tCount int(3));
 
insert into datatable(tDate,tCount)values
('2015-11-24',23),('2015-11-25',1),
('2015-11-26',21),('2015-11-28',17),
('2015-11-29',13),('2015-12-01',5),
('2015-12-02',11),('2015-12-04',8),
('2015-12-05',3),('2015-12-07',29),
('2015-12-09',80),('2015-12-10',24)

4 查询（无值时补零）

SELECT
    a.date,
    COALESCE (b.tcount, 0) count
FROM
    (
        SELECT
            adddate('2015-11-25', numlist.id) AS 'date'
        FROM
            (
                SELECT
                    n1.i + n10.i * 10 + n100.i * 100 AS id
                FROM
                    num n1
                CROSS JOIN num AS n10
                CROSS JOIN num AS n100
            ) AS numlist
        WHERE
            adddate('2015-11-25', numlist.id) <= '2015-12-25'
    ) a
LEFT JOIN datatable b ON a.date = b.tDate

查看结果：

另外一种比较笨的方法是使用union all 单独查询出来7天的数据配合 left join 条件查询出数据。

select a.click_date, ifnull(b.click_qty, 0)
from (
    select * from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
    )
) a left join (
  select click_date, count(*) as click_qty
  from click_log_table
  group by click_date --我使用的是这种方式，估计以后还得改，条件要放到left join 里面 不然没效果
) b

最后我的七天数据查询是这样写的：

SELECT
   COALESCE (b.new_add_count, 0) new_add_count,
   COALESCE (b.exit_count, 0) exit_count,
   COALESCE (b.active_count, 0) active_count
FROM
    (
        select * from (
    SELECT date_sub(curdate(), interval 1 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as statis_date
    union all
    SELECT date_sub(curdate(), interval 7 day) as statis_date
    ) d
    ) a
    LEFT JOIN stat_comm_group_day b ON a.statis_date = b.statis_date AND b.id=自己传入的参数(这儿的条件不能拿到外层。)

转载于:https://my.oschina.net/u/1024107/blog/787770