1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
-- 今天
select * from sp_pass_record where to_days(pass_time) = to_days(now());
-- 一周
select sex ss,COUNT(*) from sp_pass_record where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(pass_time) group by ss;
-- 按天统计
select DATE_FORMAT('2020-01-11','%Y%-%m-%d') days,count(*) count from sp_pass_record group by days;
-- 按周统计
select DATE_FORMAT('2020-01-10 18:31:37','%Y-%u') weeks,count(*) count from sp_pass_record group by weeks;
-- 按月统计
select DATE_FORMAT('2020-01-10 18:31:37','%Y-%m') months,count(*) count from sp_pass_record group by months;
-- 统计最近七天内的数据并按天分组
SELECT
DATE_FORMAT(pass_time, '%Y-%m-%d' ) days,
count(*) count
FROM
(SELECT * FROM sp_pass_record WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY ) <= date(pass_time) ) as da
GROUP BY days;
-- 查询一天中每一个小时的记录数量
SELECT DATE_FORMAT(pass_time, '%k' ) hour,count(*) count
FROM (select * from sp_pass_record where to_days(pass_time) = to_days(now())) as da
GROUP BY hour;
-- 查询一天中每一个小时的记录数量
SELECT HOUR(e.pass_time) as Hour,count(*) as Count
FROM sp_pass_record e
WHERE e.pass_time >= str_to_date('2020-01-17 00:00:00','%Y-%m-%d %T') AND e.pass_time < str_to_date('2020-01-17 23:59:59','%Y-%m-%d %T')
GROUP BY HOUR(e.pass_time) ORDER BY Hour(e.pass_time)
-------------------------------------------------------------------------------------------------------------------
-- 24小时统计
SELECT DATE_FORMAT(pass_time, '%k' ) hour,count(*) count
FROM sp_pass_record where to_days(pass_time) = to_days(now())-2
GROUP BY hour;
select @num:=@num+1,date_format(adddate('2020-01-19', INTERVAL @num HOUR),'%k') as hour
from sp_pass_record,(select @num:=0) t
where adddate('2020-01-19', INTERVAL @num HOUR) <= date_format('2020-01-20','%Y-%m-%d')
-- 24小时补全
select @num:=@num+1,date_format(adddate(now(), INTERVAL @num HOUR),'%k') as hour
from sp_pass_record,(select @num:=0) t
where adddate(now(), INTERVAL @num HOUR) <= date_format('2020-01-20','%Y-%m-%d')
SELECT a.num,a.hour,b.hour,IFNULL(b.count,0) count FROM
(select @num:=@num+1 as num,date_format(adddate('2020-01-18', INTERVAL @num HOUR),'%k') as hour
from sp_pass_record,(select @num:=0) t
where adddate('2020-01-18', INTERVAL @num HOUR) <= date_format('2020-01-19','%Y-%m-%d')
) a
LEFT JOIN
(SELECT DATE_FORMAT(pass_time, '%k' ) hour,count(*) count
FROM sp_pass_record
where pass_time BETWEEN '2020-01-17' and '2020-01-18' GROUP BY hour
) b
on a.hour = b.hour
|