MYSQL查询今天,昨天,这个周,上个周,这个月,上个月,今年,去年的数据

一般后台做报表什么的,可能会用到

createTime ---- 创建时间, 就是你要对比的时间,表的字段类型为 datetime

直接上代码

-- 查询上周的数据 
-- SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())-1; 

-- 查询这个周的数据
-- SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())

-- 查询上个月的数据 
-- select count(id) as count from user where date_format(createtime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') 

-- 查询这个月的数据 
-- SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m')=date_format(now(),'%Y-%m');
-- select count(id) as count from `user` where DATE_FORMAT(createtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

-- 查询距离当前现在6个月的数据 
-- select count(id) as count from user where createtime between date_sub(now(),interval 6 month) and now(); 

-- 查询今天的数据
-- SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d');

-- 查询昨天的数据
-- SELECT * FROM user WHERE TO_DAYS(NOW())-TO_DAYS(createTime) = 1

-- 今年的
-- select * from `user` where YEAR(createTime)=YEAR(NOW());
-- 去年的
-- select * from `user` where YEAR(createTime)=YEAR(NOW())-1;

-- 来一发集合的
select 
    t1.count as toDay,
    tt1.count as lastDay,
    t2.count as lastWeek,
    tt2.count as toWeek,
    t3.count as lastMonth,
    tt3.count as toMonth,
    t4.count as toYear,
    tt4.count as lastYear,
    t.count as total
    from 
(SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')) t1,
(SELECT count(id) as count FROM user WHERE TO_DAYS(NOW())-TO_DAYS(createTime) = 1) tt1,
(SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())-1) t2,
(SELECT count(id) as count FROM user WHERE YEARWEEK(date_format(createTime,'%Y-%m-%d')) = YEARWEEK(now())) tt2,
(select count(id) as count from user where date_format(createtime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')) t3,
(SELECT count(id) as count FROM user WHERE date_format(createtime,'%Y-%m')=date_format(now(),'%Y-%m')) tt3,
(select count(id) as count from `user` where YEAR(createTime)=YEAR(NOW())) t4,
(select count(id) as count from `user` where YEAR(createTime)=YEAR(NOW())-1) tt4,
(select count(id) as count from user) t

统计当前月,后12个月,各个月的数据

下面是创建对照视图

CREATE
    ALGORITHM = UNDEFINED 
    DEFINER = `tyro`@`%` 
    SQL SECURITY DEFINER
VIEW `past_12_month_view` AS
    SELECT DATE_FORMAT(CURDATE(), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 2 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 3 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 4 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 5 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 6 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 7 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 8 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 9 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 10 MONTH), '%Y-%m') AS `month` 
    UNION SELECT DATE_FORMAT((CURDATE() - INTERVAL 11 MONTH), '%Y-%m') AS `month`

然后和你想要统计的表进行关联查询,如下的demo

select 
    v.month,
    ifnull(b.minute,0) count 
from 
    past_12_month_view v 
left join 
(select DATE_FORMAT(t.createTime,'%Y-%m') month,count(t.id) minute  from user t  group by month) b 
on 
    v.month = b.month 
group by 
    v.month

结果如下

QQ图片20170418160557.png
顺便把我上次遇到的一个排序小问题也写出来

数据表有一个sort_num 字段来代表排序,但这个字段有些值是null,

现在的需求是

返回结果集按升序返回,如果sort_num 为null 则放在最后面

mysql null 默认是最小的值,如果按升序就会在前面

解决方法

SELECT * from table_name 
ORDER BY 
  case 
WHEN 
  sort_num is null 
  then 
    1 
  else 0 end, sort_num asc

搞定收工。。。。。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 1. Java基础部分 基础部分的顺序:基本语法,类相关的语法,内部类的语法,继承相关的语法,异常的语法,线程的语...
    子非鱼_t_阅读 31,780评论 18 399
  • 1.MySQL数据库 2.SQL语句 第一节课 ###1(MySQL数据库)数据库概念.avi 5...
    码了个农啵阅读 1,247评论 1 16
  • 系统层面(基本不用动,看了下,买的云服务器基本都已经优化过了) 内核相关参数(/etc/sysctl.conf) ...
    神奇大叶子阅读 2,057评论 0 4
  • 一、模型 首先是『提示→惯例→奖励→提示』的习惯循环。 惯例是我们想养成的习惯行为,『提示』、『奖励』和重复『循环...
    HealthPet阅读 605评论 0 1
  • 曾经以为我很幸运 在情窦初开的时候就遇到了你 只是像一阵呼啸而过的风 留我独自凄清 像往常一样,习惯了早起,自从那...
    超人会飞啊阅读 193评论 0 1