MySQL查询每天每周每月每季每年的数据等常用统计查询

忘是亡心i 2021-11-23 09:16 491阅读 0赞

查询每天的数据：

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,  
    QUARTER(createTim) as dateTime  
FROM  
    testTable  
GROUP BY QUARTER(createTime)

查询每年的数据：

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

\-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

今天

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 \`ht\_invoice\_information\` where QUARTER(create\_date)=QUARTER(now());  
\#查询上季度数据  
select \* from \`ht\_invoice\_information\` where QUARTER(create\_date)=QUARTER(DATE\_SUB(now(),interval 1 QUARTER));  
\#查询本年数据  
select \* from \`ht\_invoice\_information\` where YEAR(create\_date)=YEAR(NOW());  
\#查询上年数据  
select \* from \`ht\_invoice\_information\` where year(create\_date)=year(date\_sub(now(),interval 1 year));

\-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

查询当前这周的数据   
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date\_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据  
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date\_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询当前月份的数据  
select name,submittime from enterprise   where date\_format(submittime,'%Y-%m')=date\_format(now(),'%Y-%m')

查询距离当前现在6个月的数据  
select name,submittime from enterprise where submittime between date\_sub(now(),interval 6 month) and now();

查询上个月的数据  
select name,submittime from enterprise   where date\_format(submittime,'%Y-%m')=date\_format(DATE\_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')

\-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

select \* from \` user \` where DATE\_FORMAT(pudate, ' %Y%m ' ) = DATE\_FORMAT(CURDATE(), ' %Y%m ' ) ;

select \* from user where WEEKOFYEAR(FROM\_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())

select \*   
from user   
where MONTH (FROM\_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())

select \*   
from \[ user \]   
where YEAR (FROM\_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())  
and MONTH (FROM\_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())

select \*   
from \[ user \]   
where pudate between 上月最后一天  
and 下月第一天

where   date(regdate)   =   curdate();

select   \*   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())

SELECT date( c\_instime ) ,curdate( )  
FROM \`t\_score\`  
WHERE 1  
LIMIT 0 , 30

\-------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

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

一、年度查询  
查询 本年度的数据

SELECT \*  
FROM blog\_article  
WHERE year( FROM\_UNIXTIME( BlogCreateTime ) ) = year( curdate( ))

二、查询季度数据  
查询数据附带季度数

SELECT ArticleId, quarter( FROM\_UNIXTIME( \`BlogCreateTime\` ) )  
FROM \`blog\_article\`

查询 本季度的数据

SELECT \*  
FROM blog\_article  
WHERE quarter( FROM\_UNIXTIME( BlogCreateTime ) ) = quarter( curdate( ))

三、查询月度数据  
本月统计(MySQL)

select \* from booking where month(booking\_time) =  
month(curdate()) and year(booking\_time) = year(curdate())

本周统计(MySQL)

select \* from spf\_booking where month(booking\_time) =  
month(curdate()) and week(booking\_time) = week(curdate())

四、时间段

N天内记录

WHERE TO\_DAYS(NOW()) - TO\_DAYS(时间字段) <= N

当天的记录

where date(时间字段)=date(now())  
\---或  
where to\_days(时间字段) = to\_days(now());

查询一周：

select \* from table   where DATE\_SUB(CURDATE(), INTERVAL 7 DAY) <= date(column\_time);

查询一个月：

select \* from table where DATE\_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(column\_time);

查询'06-03'到'07-08'这个时间段内所有过生日的会员：

Select \* From user Where  
DATE\_FORMAT(birthday,'%m-%d') >= '06-03' and DATE\_FORMAT(birthday,'%m-%d')  
<= '07-08';

统计一季度数据，表时间字段为：savetime

group by concat(date\_format(savetime, '%Y '),FLOOR((date\_format(savetime, '%m ')+2)/3))  
\---或  
select YEAR(savetime)\*10+((MONTH(savetime)-1) DIV 3) +1,count(\*)  
from yourTable  
group by YEAR(savetime)\*10+((MONTH(savetime)-1) DIV 3) +1;

五、分组查询  
   1、年度分组  
   2、月度分组  
   3、先按年度分组，再按月度分组  
   4、按年月分组

SELECT count(ArticleId), date\_format(FROM\_UNIXTIME( \`BlogCreateTime\`),'%y%m') sdate  FROM \`blog\_article\` group by sdate

结果：  
count( ArticleId )     sdate  
17     0901  
11     0902  
5      0903  
6     0904  
2     0905  
1     0907  
12     0908  
6     0909  
11     0910  
3     0911

其他方法参考：  
做一个统计，[数据库][Link 1]是[mysql][]，统计出每天，每周，每月的记录数  
建表的时候加个字段表示日期。

方法1，

select count(\*) from \`table\` where \`date\`='\{某天\}'  
select count(\*) from \`table\` where date\_format(\`date\`,'%V')='\{某周\}'  
select count(\*) from \`table\` where date\_format(\`date\`,'%c')='\{某月\}'

方法2，

select count( \* ) from projects where editdate >= '2007-11-9 00:00:00' and editdate <=  
'2007-11-9 24:00:00';

方法3，  
每周的

select count(\*) as cnt,week(editdate) as weekflg from projects where year(editdate)  
=2007 group by weekflg

每月

select count(\*) as cnt,month(editdate) as monthflg from projects where year  
(editdate)=2007 group by monthflg

每天

select count(\*) as cnt from projects group by date(editdate)

mysql中DATE\_FORMAT(date, format)函数可根据format字符串格式化日期或日期和时间值date，返回结果串。  
也可用DATE\_FORMAT( ) 来格式化DATE 或DATETIME 值，以便得到所希望的格式。根据format字符串格式化date值:

函数的参数说明:  
%S, %s 两位数字形式的秒（ 00,01, . . ., 59）  
%i 两位数字形式的分（ 00,01, . . ., 59）  
%H 两位数字形式的小时，24 小时（00,01, . . ., 23）  
%h, %I 两位数字形式的小时，12 小时（01,02, . . ., 12）  
%k 数字形式的小时，24 小时（0,1, . . ., 23）  
%l 数字形式的小时，12 小时（1, 2, . . ., 12）  
%T 24 小时的时间形式（hh : mm : s s）  
%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）  
%p AM 或P M  
%W 一周中每一天的名称（ Sunday, Monday, . . ., Saturday）  
%a 一周中每一天名称的缩写（ Sun, Mon, . . ., Sat）  
%d 两位数字表示月中的天数（ 00, 01, . . ., 31）  
%e 数字形式表示月中的天数（ 1, 2， . . ., 31）  
%D 英文后缀表示月中的天数（ 1st, 2nd, 3rd, . . .）  
%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, . . ., 6=Saturday）  
%j 以三位数字表示年中的天数（ 001, 002, . . ., 366）  
% U 周（0, 1, 52），其中Sunday 为周中的第一天  
%u 周（0, 1, 52），其中Monday 为周中的第一天  
%M 月名（January, February, . . ., December）  
%b 缩写的月名（ January, February, . . ., December）  
%m 两位数字表示的月份（ 01, 02, . . ., 12）  
%c 数字表示的月份（ 1, 2, . . ., 12）  
%Y 四位数字表示的年份  
%y 两位数字表示的年份  
%% 直接值“%”

[Link 1]: http://www.jbxue.com/db/
[mysql]: http://www.jbxue.com/mysql/