mysql一些实用的统计(当日,当月,不重复行等)
最近做到项目的首页展示数据的部分,需要用到各式各样的统计数据,在这里总结一下
//当天数据 SELECT * FROM 表名 WHERE TO_DAYS(now()) = TO_DAYS(时间字段名) //当月数据 SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format(now() , '%Y%m') , date_format(时间字段名, '%Y%m')) =0 //这里解释一下,PERIOD_DIFF是返回两个时间段之间的月数,所以这里是相差月数等于0,这个也可以用来做前某个月的统计,此外,也可以用DATE_SUB函数,如:
SELECT * FROM 表名 WHERE DATE_SUB(now(),INTERVAL 7 DAY) <= 时间字段名
表示近七天的数据
//不重复行统计 SELECT count(DISTINCT id) FROM 表名 //按天统计 SELECT count(id) countNum, DATE(create_time) createTime FROM 表名 GROUP BY DATE(create_time) ORDER BY DATE(create_time) DESC; //按周统计 SELECT count(id) countNum, WEEK(create_time) createTime FROM 表名 GROUP BY WEEK(create_time) ORDER BY WEEK(create_time) DESC; //按月统计 SELECT count(id) countNum, MONTH(create_time) createTime FROM 表名 GROUP BY MONTH(create_time) ORDER BY MONTH(create_time) DESC; //按季度统计 SELECT count(id) countNum, QUARTER(create_time) createTime FROM 表名 GROUP BY QUARTER(create_time) ORDER BY QUARTER(create_time) DESC; //按年统计 SELECT count(id) countNum, YEAR(create_time) createTime FROM 表名 GROUP BY YEAR(create_time) ORDER BY YEAR(create_time) DESC;