一、查询每天的数据,没有数据的那天,补全0:
SELECT
dateTime,
MAX( success ) AS success,
MAX( fail ) AS fail
FROM
(
SELECT
@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) dateTime,
0 AS success,
0 AS fail
FROM
( SELECT @cdate := DATE_ADD( #{date1}, INTERVAL + 1 DAY ) FROM table ) temp
WHERE
@cdate > #{date2} UNION ALL
SELECT
DATE_FORMAT( t1.create_time, '%Y-%m-%d' ) AS dateTime,
sum( CASE WHEN t1.status = 1 THEN 1 ELSE 0 END ) AS success,
sum( CASE WHEN t1.status != 1 THEN 1 ELSE 0 END ) AS fail
FROM
table t1
LEFT JOIN table2 t2 ON t1.id = t2.t1_id
WHERE
t1.create_time >= #{date3}
and t1.create_time <= #{date4}
GROUP BY
DATE_FORMAT( t1.create_time, '%Y-%m-%d' )
) tempAllTable
GROUP BY
dateTime
ORDER BY
dateTime DESC
查询结果展示:
- #{date1} 该字段为创建临时表的起始时间。DATE_ADD(date,INTERVAL expr type)INTERVAL +1 DAY表示起始日期添加1天。
- *创建临时表,是以业务表为基础创建的临时表。例子中message_record有很多数据,是为了省事创建。如果业务表没有数据,这里创建日期临时表为空。如果只有10条数据,那么临时表中也只有10条数据。
- select 中查询@cdate := DATE_ADD( @cdate, INTERVAL - 1 DAY ) dateTime 表示从临时表中查询每次减1天的日期。
- #{date2} 该字段为临时表查询的截止日期,即每次减1天,到#{date2}日期截止。
- #{date3}、#{date4} 为业务表中判断时间段的起始和结束时间。
- 一般情况补全天的话。临时表起始日期#{date1} = 业务表截止日期#{date4} 。临时表截止日期#{date2} = 业务表起始日期#{date3}。
- success和fail数据是根据状态做的分类统计。如果不需要分类,只需要统计数量,这里可以替换count()函数
二、查询每月的数据,没有数据的月份,补全0:
SELECT
dateTime,
MAX( success ) AS success,
MAX( fail ) AS fail
FROM
(
SELECT
DATE_FORMAT( @cdate := DATE_ADD( @cdate, INTERVAL - 1 MONTH ), '%Y-%m' ) dateTime,
0 AS success,
0 AS fail
FROM
( SELECT @cdate := DATE_ADD( #{date1}, INTERVAL + 1 MONTH ) FROM table LIMIT #{limit}) temp
UNION ALL
SELECT
DATE_FORMAT( t1.time_create, '%Y-%m' ) AS dateTime,
sum( CASE WHEN status = 1 THEN 1 ELSE 0 END ) AS success,
sum( CASE WHEN status != 1 THEN 1 ELSE 0 END ) AS fail
FROM
table t1
LEFT JOIN table t2 mr ON t1.id = t2.t1_id
WHERE
t1.time_create >= #{date2}
AND t1.time_create <= #{date3}
GROUP BY
DATE_FORMAT( t1.time_create, '%Y-%m' )
) tempAllTable
GROUP BY
dateTime
ORDER BY
dateTime DESC
查询结果展示:
- #{date1} 类似于查询天中的起始时间,时间间隔为 1 MONTH。
- #{date2}、#{date3}为业务表查询比较日期。
- #{limit} 为查询结果中限制数量。这个条件也可以在查询天的SQL中添加。这里添加#{limit}是为了查询月数据准确。不加limit,只有@cdate< #{date} 的话,这里比较的是天。但是临时表计算日期是INTERVAL -1 MONTH。如图: