Mysql常用的统计查询语句

数据统计

select * from table where DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= date(createtime); 七天之前数据 30天
select * from table where to_days(createtime) =to_days(now()); 今天数据
select * from table where TO_DAYS( NOW( ) ) -TO_DAYS( createtime) <= 1 昨天今天数据
select * from table where DATE_FORMAT( createtime,'%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )本月
select * from table where PERIOD_DIFF( date_format(now( ) , '%Y%m' ) , date_format( createtime, '%Y%m' ) ) =1  上月
select * from table where YEARWEEK(date_format(createtime,'%Y-%m-%d')) =YEARWEEK(now()); 本周(第一天星期日)
select * from table where YEARWEEK(date_format(createtime,'%Y-%m-%d')) = YEARWEEK(now())-1; 上周数据

字段标记和排序

排名会重名
select id,zan,(select count(*)+1 as rank from  table_name as B where B.zan>A.zan) as rank from  table_name as A order by zan desc; //排名 出现1 2 3 4 4  6占位

SET @rank=0;
SELECT id,@rank:=@rank+1 as rank,zan  FROM table_name ORDER BY zan DESC;//排名 1,2,3,4,5,6  相同的分数也会排名

更新表排名
update table_name as M,(select id,(select count(*)+1 as rank from table_name as B where B.zan>A.zan) as   rank from table_name A) as N set M.tops=N.rank where  M.id=N.id;//更新表排名

随机一条
SELECT FROM table_name ORDER BY RAND() LIMIT 1;//随机取的一条 0.016-0.024

`SELECT FROM table_name AS A JOIN (SELECT ROUND(RAND() * ((SELECT MAX(id) FROM  table_name)-(SELECT MIN(id) FROM     table_name))+(SELECT MIN(id) FROM table_name)) AS id) AS B WHERE A.id >= B.id ORDER BY A.id LIMIT 1;` //高效率0.003

自动加一
UPDATE table_name SET tops = tops+1 WHERE id=5