mysql 统计最近几天的数据(没数据展示零)

一、mysql 统计最近几天的数据(当天没数据展示零)

1、先上效果

2、新建num表

3、sql展示

 <!--统计最近九天的告警信息-->
  SELECT
            COUNT(a.`value`) value, n.tdate
        FROM
            ( SELECT  flogId as `value`, DATE_FORMAT( warningTime, '%Y-%m-%d') as date ,deviceId
                FROM warning_log GROUP BY warningTime ) as  a
            RIGHT JOIN (
            -- 获取最近九天的日期
                SELECT
                    date_format( DATE_SUB(CURDATE(), INTERVAL n.i DAY),
                    '%Y-%m-%d' ) AS tdate
                FROM num n
                							
                WHERE 10 > i
                ORDER BY i
            ) n ON a.date = n.tdate
            -- 业务关联 获取指定地区的告警
            LEFT JOIN device d on a.deviceId=d.deviceId
            WHERE d.areaId in (${areaIds}) or d.areaId is NULL
        GROUP BY    n.tdate