mysql查询时间为24小时内_mysql 日期查询 总结

1，对一天24小时内每小时的数据量求和：

SELECT

IFNULL(SUM(CASE HOUR(时间字段) WHEN 0 THEN 1 ELSE 0 END),0)as '0',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 1 THEN 1 ELSE 0 END),0)as '1',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'

FROM 表名称

WHERE date(时间字段) >= CURDATE() and date(时间字段) <= CURDATE()

2，对昨天24小时内每小时的数据量求和：

SELECT

IFNULL(SUM(CASE HOUR(时间字段) WHEN 0 THEN 1 ELSE 0 END),0)as '0',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 1 THEN 1 ELSE 0 END),0)as '1',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 2 THEN 1 ELSE 0 END),0) AS '2',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 3 THEN 1 ELSE 0 END),0) AS '3',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 4 THEN 1 ELSE 0 END),0) AS '4',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 5 THEN 1 ELSE 0 END),0) AS '5',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 6 THEN 1 ELSE 0 END),0) AS '6',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 7 THEN 1 ELSE 0 END),0) AS '7',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 8 THEN 1 ELSE 0 END),0) AS '8',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 9 THEN 1 ELSE 0 END),0) AS '9',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 10 THEN 1 ELSE 0 END),0) AS '10',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 11 THEN 1 ELSE 0 END),0) AS '11',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 12 THEN 1 ELSE 0 END),0) AS '12',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 13 THEN 1 ELSE 0 END),0) AS '13',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 14 THEN 1 ELSE 0 END),0) AS '14',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 15 THEN 1 ELSE 0 END),0) AS '15',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 16 THEN 1 ELSE 0 END),0) AS '16',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 17 THEN 1 ELSE 0 END),0) AS '17',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 18 THEN 1 ELSE 0 END),0) AS '18',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 19 THEN 1 ELSE 0 END),0) AS '19',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 20 THEN 1 ELSE 0 END),0) AS '20',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 21 THEN 1 ELSE 0 END),0) AS '21',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 22 THEN 1 ELSE 0 END),0) AS '22',

IFNULL(SUM(CASE HOUR(时间字段) WHEN 23 THEN 1 ELSE 0 END),0) AS '23'

FROM 表名称

WHERE date(access_time) >= date_sub(CURDATE() , INTERVAL 1 DAY) AND date(access_time) < CURDATE()

这个红色的1 就是前进的天数，比如说昨天 是1，前天就是2，以此类推。

效果：

3，对一天内的数据量求和查询：

我们需要生成一个日历的表，然后和原来的数据，联合查询，如下：

CREATE TABLE if not exists calendar(datelist date); -- 生成一个存储日期的表，datalist是字段名

-- 这里是生成并插入日期数据

INSERT INTO calendar(datelist) SELECT

adddate(

( -- 这里的起始日期，你可以换成当前日期

DATE_FORMAT("2016-1-1", '%Y-%m-%d')

),

numlist.id

) AS `date`

FROM

(

SELECT

n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id

FROM

num n1

CROSS JOIN num AS n10

CROSS JOIN num AS n100

CROSS JOIN num AS n1000

CROSS JOIN num AS n10000

) AS numlist;

联合查询sql如下：

SELECT

date(dday) ddate,

count(*) - 1 as num

FROM

(

SELECT

datelist as dday

FROM

calendar_data

-- 这里是限制返回最近7天的数据

where DATE_SUB(CURDATE(), INTERVAL 6 DAY)<= date(datelist)&&date(datelist)<=CURDATE()UNION ALL

SELECT

时间字段

FROM

表名称

where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(时间字段)AND date(时间字段) <= CURDATE()and access_time is not null

) a

GROUP BY ddate

结果如下：

上面的6是控制查询的天数，有需要更改条件即可。

3，对限定日期内的数据去重复求和查询：

SELECT

DATE_FORMAT(时间字段,'%Y-%m-%d') as ddate,

count(DISTINCT 去重复字段) as num

FROM

表名

where DATE_SUB(CURDATE(), INTERVAL 7 DAY)<= date(时间字段) AND date(时间字段) <= CURDATE()and 时间字段 is not null

GROUP BY

ddate

结果：