说明
在项目开发里面,有时候经常要根据时间对数据做统计返回给前端,如果只取一段范围内的时间查询,只返回数据库中查询到的到还简单,如果要把没有的日期也统计出来那么就感觉稍微麻烦了一点。
pgsql语句写法:
一般sql语句查询如下:
其中我的starttime在数据库中类型是timestamp
to_char()将日期转换为字符串
我这里查询的时间是2020-06-20到2020-06-30的时间段,发现只查到了三天的数据:
很明显这样写不是我所期望的结果,我期望的是2020-06-20到2020-06-27号的也能查出来并补0。
如果要实现我的那种期望结果,需要用到这个函数,如下:
补充
select date(t) as day
from
generate_series('2020-06-20'::date,'2020-06-30', '1 days') as t
它能将你的开始日期到结束日期之间的日期都能显示出来
实现
将上面的二者结合,联表查询发现就查询到了自己期望的结果。
在实际开发中只需要将2个日期2020-06-20和2020-06-30换成对应的开始日期参数和结束日期参数,那么这个统计结果就是符合期望的结果的了。
SELECT a.time,COALESCE(b.counts,0) as counts from
(
SELECT
to_char(b,'yyyy-MM-dd') AS time
FROM
generate_series('2020-06-20'::date,'2020-06-30','1 days') AS b
GROUP by time
ORDER BY time
) as a
LEFT JOIN
(
select to_char(starttime,'yyyy-MM-dd') AS starttime, count(starttime) AS counts
from rnodbv2.v2_m_ctest_log_info
where to_char(starttime,'yyyy-MM-dd')>='2020-06-20' and to_char(starttime,'yyyy-MM-dd')<='2020-06-30'
GROUP BY to_char(starttime,'yyyy-MM-dd')
) as b
on a.time=b.starttime
order by a.time
结果如下:
mysql语句写法:
一般sql语句查询如下:
其中我的create_time在数据库中类型是datetime
– 将日期转化为字符串
DATE_FORMAT(CREATE_TIME,’%Y-%m-%d %H:%i:%s’)
– 将字符串转化为日期
STR_TO_DATE(‘2020-09-04 16:49:05’, ‘%Y-%m-%d %H:%i:%s’)
我这里查询的时间是2020-10-20到2020-10-30的时间段,发现只查到了二天的数据:
这里我使用这个语句来做一个虚拟的日期表
SELECT @cdate := DATE_ADD(@cdate,INTERVAL - 1 DAY) date
FROM (SELECT @cdate :=DATE_ADD('2020-10-29', INTERVAL + 1 DAY) FROM sys_user) t1
WHERE @cdate>'2020-10-01'
注意:这个sql有个局限性,那就是你查的表的数据行数必须要大于你的日期天数,它才能显示你时间范围内的所有日期,因为我的sys_user表里面只有9条数据,所以它只会返回最后9天的日期,如果这个表数据有29条以上,那么就能显示1-29号的日期
sys_user表:
实现
将上面的二者结合,联表查询发现就查询到了自己期望的结果。
select a.date, ifnull(b.counts,0) as counts
from
(select @cdate := date_add(@cdate,INTERVAL - 1 DAY) as date
from (select @cdate :=date_add('2020-10-29', INTERVAL + 1 DAY) from sys_user) t1
where @cdate>'2020-10-01') as a
left join
(select date_format(create_time,'%Y-%m-%d') create_time,count(create_time) as counts
from sys_user
where date(create_time)>='2020-10-01' and date(create_time)<='2020-10-29'
group by date_format(create_time,'%Y-%m-%d')
order by date_format(create_time,'%Y-%m-%d')) as b
on a.date=date(b.create_time)
order by a.date
结果如下:
当然前提是sys_user表的数据行数大于29条日期才能显示完全。实际开发一般情况下表数据不会像我这么少吧,我这里只是测试。我只是不想在创建一个日期表才这样处理,为什么mysql就没有pgsql那样的一个系统函数呢,给一个开始日期和结束日期就能生成一个虚表日期。