常用SQL整理

常用SQL整理

查询当天、本周、本月、本年的数据

查询天

1
2
# 查询当天
select * from tb where DATE(create_time) = curdate()

查询周

1
2
3
4
5
6
7
# 查询本周(从上个星期的周日开始计算，到这个星期的周六)
select * from tb where YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())
# 查询上周
SELECT * FROM tb WHERE WEEK(create_time)=WEEK(DATE_SUB(NOW(),INTERVAL 1 WEEK))
AND YEAR(create_time) = YEAR(NOW()) 
# 查询近七天的数据
select * from tb where date_sub(curdate(), interval 7 day) <= date(create_time)

查询月

1
2
3
4
5
# 查询本月
select * from tb where date_format(create_time,'%Y-%m') = date_format(now(),'%Y-%m')
# 查询上月的数据
select * from tb
	where date_format(create_time, '%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

查询季度

1
2
3
4
5
# 查询本季度
SELECT * FROM tb WHERE QUARTER(create_time)=QUARTER(NOW())
AND YEAR(create_time) = YEAR(NOW())
# 查询上季度
SELECT * FROM tb WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) AND YEAR(create_time) = YEAR(NOW())

查询年

1
2
3
4
# 查询本年
select * from tb where date_format(create_time,'%Y') = date_format(now(),'%Y')
# 查询上年
SELECT * FROM tb WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
编程笔记 数据库
SQL

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！