您的位置:威尼斯官方网站 > 威尼斯官方网站登录 > 当天2017-08-28结果显示为NULL

当天2017-08-28结果显示为NULL

发布时间:2019-11-21 16:23编辑:威尼斯官方网站登录浏览(187)

    问题描述

    查询数据库表中最近7天的记录

    select count(*),date(create_time) as date from task where datediff(now(),create_time)<=6  group by day(create_time); 
    

     

    但是发现某一天没有数据,结果中没有显示当天(2017-08-28)的数据

    图片 1

    解决思路

    1. 思路一: 可以在自己的程序中做额外的补零处理

    2. 思路二: 构建一个最近七天的结果集,然后和查询的结果集合做left join(本文采用第二种方式)

    select a.click_date,b.count
    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 date(create_time) as datetime, count(*) as count
      from arms_task
      group by date(create_time)
    ) b on a.click_date = b.datetime;
    

    当天2017-08-28结果显示为NULL

    图片 2

    需要把NULL设置为0,利用ifnull函数即可

    select a.click_date,ifnull(b.count,0) as count
    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 date(create_time) as datetime, count(*) as count
      from arms_task
      group by date(create_time)
    ) b on a.click_date = b.datetime;
    

     

    图片 3

    本文由威尼斯官方网站发布于威尼斯官方网站登录,转载请注明出处:当天2017-08-28结果显示为NULL

    关键词:

上一篇:没有了

下一篇:没有了