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

 

结果：