mysql高级查询按当天、昨天、每年、每月、每日、每周、近七天、近三十天、当前这周、上周、本季度、上季度统计用户数量

目录

1.年统计sql

2.月统计sql

3.日统计sql

4.周期统计sql

5.当天sql

6.昨日sql

7.近期7天sql

8.近30天sql

9.本季度sql

10.上季度sql

11.查询当前这周的数据

12.查询上周

13.统计近十年每年的数据量

14.统计近20天每天的数据量

15.随机查询

16.每隔五分钟统计一次

---

Mybatis中的sql语句中的“<”和“>”要用转义字符“<”和”>“，否则会报错！

&lt;=  小yu等于

&gt;=  大于等于

1.年统计sql

SELECT DISTINCT(DATE_FORMAT(create_time,'%Y')) as year ,count(*)  as yearcount from user   GROUP BY year

2.月统计sql

SELECT DISTINCT(DATE_FORMAT(create_time,'%Y-%m')) as month ,count(*)  as monthcount from user   GROUP BY month

3.日统计sql

SELECT DISTINCT(DATE_FORMAT(create_time,'%Y-%m-%d')) as day ,count(*)  as daycount from user   GROUP BY day

4.周期统计sql

SELECT DISTINCT(DATE_FORMAT(create_time,'%Y-%u')) as week ,count(*)  as weekcount from user   GROUP BY week

5.当天sql

可以把COUNT(1) as today换成需要的字段

select COUNT(1) as today from 表名 where to_days(时间字段名) = to_days(now());

6.昨日sql

可以把COUNT(1) asYesterday换成需要的字段

select COUNT(1) as Yesterday from 表名 where to_days(now())-to_days(时间字段名) <= 1

7.近期7天sql

可以把COUNT(1) as 7days换成需要的字段

select COUNT(1) as 7days from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)

8.近30天sql

把COUNT(1) as 30days 换成需要的字段

select COUNT(1) as 30days from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)

9.本季度sql

把COUNT(1) as ThisQuarter换成需要的字段

select COUNT(1) as ThisQuarter from 表名 where QUARTER(时间字段名)=QUARTER(now());

10.上季度sql

把COUNT(1) as LastQuarter换成需要的字段

select COUNT(1) as LastQuarter from 表名 where QUARTER(时间字段名)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

11.查询当前这周的数据

把COUNT(1) as thisWeek换成需要的字段

SELECT COUNT(1) as thisWeek FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now());

12.查询上周

把COUNT(1) as lastWeek换成需要的字段

SELECT COUNT(1) as lastWeek FROM 表名 WHERE YEARWEEK(date_format(时间字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

13.统计近十年每年的数据量

SELECT DATE_FORMAT(create_time,'%Y') as year ,count(*) as count from 表名
        where date(时间字段名) >=date_sub(curdate(), interval 10 YEAR) GROUP BY YEAR
        ORDER BY YEAR asc

14.统计近20天每天的数据量

SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as day ,count(*) as count from 表名
        where date(时间字段名) >=date_sub(curdate(), interval 20 DAY) GROUP BY DAY
        ORDER BY DAY asc

15.随机查询

select * from user order by RAND()
select * from user order by RAND() LIMIT 100// 随机查询100条

16.每隔五分钟统计一次

SELECT concat( date_format(create_time, '%Y-%m-%d %H:' ) , floor( date_format(create_time, '%i' ) /5 ) ) AS 时间段, count( * ) as 数量
FROM 表名
GROUP BY 时间段