统计近七天数据,没有自动补0

mysql 统计近七天的数据 没有数据的天数自动补0

``## 没有补0 的数据`

SELECT
        COUNT(1) counts,
        DATE_FORMAT(h.alarm_time, '%Y-%m-%d') dataTime
        FROM
        location_alarm_history h
        LEFT JOIN hxl_location_user u ON h.card_id = u.CARD_NO  AND u.USER_TYPE IS NOT NULL
        LEFT JOIN sys_dict dc ON dc.value= u.USER_TYPE
        AND dc.type = 'user_type'
        WHERE
        DATE_FORMAT(h.alarm_time, '%Y-%m-%d') <= DATE_FORMAT(NOW(), '%Y-%m-%d')
        AND DATE_FORMAT(h.alarm_time, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 7 DAY), '%Y-%m-%d')
       
       GROUP BY DATE_FORMAT(h.alarm_time, '%Y-%m-%d')

• 效果如下：
• 
• 构建一个最近七天的结果集，然后和查询的结果集合使用IFNULL(expression, alt_value) 做为null
• 赋值，代码如下:

SELECT a.click_date dayTime,ifnull(b.count,0) counts
        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
        COUNT(1) count,
        DATE_FORMAT(h.alarm_time, '%Y-%m-%d') bDataTime
        FROM
        location_alarm_history h
        LEFT JOIN hxl_location_user u ON h.card_id = u.CARD_NO  AND u.USER_TYPE IS NOT NULL
        LEFT JOIN sys_dict dc ON dc.value= u.USER_TYPE
        AND dc.type = 'user_type'
        WHERE
        DATE_FORMAT(h.alarm_time, '%Y-%m-%d')  <= DATE_FORMAT(NOW(), '%Y-%m-%d')
        AND DATE_FORMAT(h.alarm_time, '%Y-%m-%d') >= DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 6 DAY), '%Y-%m-%d')
        GROUP BY DATE_FORMAT(h.alarm_time, '%Y-%m-%d') ) b ON a.click_date = b.bDataTime
        ORDER BY a.click_date asc

• 效果如下：
•