使用mysql数据库查询今天、昨天、本周、本月、上一月 、今年数据的sql语句总结

当天

select id,create_time from mdz_sale_order where to_days(create_time) = to_days(now());

昨天

SELECT id,create_time FROM mdz_sale_order WHERE TO_DAYS( NOW( )) - TO_DAYS(create_time) =1;

本周

SELECT id,create_time FROM mdz_sale_order WHERE WEEKOFYEAR(create_time)=WEEKOFYEAR(NOW()) ;

近七天

SELECT id,create_time FROM mdz_sale_order where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time)

本月

SELECT id,create_time FROM mdz_sale_order WHERE DATE_FORMAT( create_time, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) ,’%Y%m’ )

上个月

第一种写法

SELECT id,create_time FROM mdz_sale_order WHERE PERIOD_DIFF(date_format(now(),’%Y%m’),date_format(create_time,’%Y%m’) )=1

第二种写法

SELECT id,create_time FROM mdz_sale_order WHERE DATE_FORMAT( create_time, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) ,’%Y%m’ ) -1

mysql 查询当天、本周，本月，上一个月的数据

今天

SELECT * from 表名称 where to_days(时间字段名) = to_days(now());

昨天

SELECT * FROM 表名称 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1

近7天

SELECT * FROM 表名称 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天

SELECT * FROM 表名称 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

本月

SELECT * FROM 表名称 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

上一月

SELECT * FROM 表名称 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

查询本季度数据

SELECT * from `qipa250` where QUARTER(create_time)=QUARTER(now());

查询上季度数据

SELECT * from `qipa250` where QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

SELECT * from `qipa250` where YEAR(create_time)=YEAR(NOW());

查询上年数据

SELECT * from `qipa250` where year(create_time)=year(date_sub(now(),interval 1 year));

查询当前这周的数据

SELECT name,submittime FROM qipa250 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT name,submittime FROM qipa250 WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询上个月的数据

SELECT name,submittime from qipa250 where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

SELECT * from qipa250 where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

SELECT * from qipa250 where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 

SELECT * from qipa250 where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

SELECT * from qipa250 where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now()) 

SELECT * from qipa250 where pudate between  上月最后一天  and 下月第一天 

查询当前月份的数据

SELECT name,submittime from qipa250   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

SELECT name,submittime from qipa250 where submittime between date_sub(now(),interval 6 month) and now();

查询每天每周每月每年的总数

查询每天的数据：

SELECT
    COUNT(1) AS countNumber,
    DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime
FROM
    testTable
GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')

查询每周的数据：

SELECT
    COUNT(1) AS countNumber,
    WEEK(createTime) as dateTime
FROM
    testTable
GROUP BY WEEK(createTime) 

查询每月的数据：

SELECT
    COUNT(1) AS countNumber,
    MONTH(createTime) as dateTime
FROM
    testTable
GROUP BY MONTH(createTime)

查询每年的数据：

SELECT
    COUNT(1) AS countNumber,
    YEAR(createTime) as dateTime
FROM
    testTable
GROUP BY YEAR(createTime)

其它：

DATE_ADD(createTime,INTERVAL 1 DAY)           得到指定日期后一天的日期/把1改为任意数字就可以得到后N天的日期

DATE_SUB(createTime,INTERVAL 1 DAY)           得到指定日期前一天的日期/把1改为任意数字就可以得到前N天的日期

声明变量查询：

SET @counter=0;
 select @counter:=@counter+1 as id,count(*) as num FROM testTable ORDER BY num desc