SQL语法--获取统计数据

阅读531评论0

近期在写统计,就把SQL统计的语法整理记录一下。

我们会经常用到统计,统计的语句写的不好,那速度有点让人着急,特别是数据大的时候、、、

下面的语句不敢保存每一个都是最优的,有好的写法的可以写到留言那里,大家一起进步


PS:下面所有的时间在数据库都是 2017-06-07 11:12:13 这种格式保存的,不是这种格式的朋友记得转换下(date_format)。


1、按年度统计

S E LEC T * FROM MyDataSheet WHERE year(createTime) = year(now())


2、按季度统计

S E LEC T * FROM MyDataSheet WHERE quarter(createTime) = quarter(now())


3、本月统计

S E LEC T * FROM MyDataSheet WHERE month(createTime) = month(now()) and year(createTime) = year(now())


4、本周统计

/*方法一*/ 

S E LEC T * FROM MyDataSheet WHERE month(createTime) = month(now()) and week(createTime) = week(now()) 

/*方法二*/ 

S E LEC T * FROM MyDataSheet WHERE YEARWEEK(createTime) = YEARWEEK(now())


5、统计一周(7天)

S E LEC T * FROM MyDataSheet WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(createTime);


6、统计N天以内

S E LEC T * FROM MyDataSheet WHERE TO_DAYS(NOW()) - TO_DAYS(createTime) <= N天


7、统计当天

/*方法一*/ 

S E LEC T * FROM MyDataSheet where date(createTime) = date(now())

 /*方法二*/ 

S E LEC T * FROM MyDataSheet where to_days(createTime) = to_days(now())


8、统计一个月

S E LEC T * FROM MyDataSheet WHERE DATE_SUB(CURDATE(), INTERVAL INTERVAL 1 MONTH) <= date(createTime);


9、按年度统计

S E LEC T YEAR(createTime) as years,count(*) FROM MyDataSheet group by years;


10、按季度分组统计

S E LEC T YEAR(createTime)*10+((MONTH(createTime)-1) DIV 3)+1 as quarter,count(*) FROM MyDataSheet group by quarter;


11、按月分组统计

S E LE T MONTH(createTime) as months,count(*) FROM MyDataSheet group by months;


12、按年月分组统计

/*2017-06-07 11:12:13格式*/ 

S E LEC T count(*), DATE_FORMAT(DATE_SUB(createTime),'%y%m') sdate FROM MyDataSheet group by sdate 

/*1496805133格式*/ 

S E LEC T count(*), DATE_FORMAT(FROM_UNIXTIME(createTime),'%y%m') sdate FROM MyDataSheet group by sdate


13、按天分组统计

S E LEC T count(*), DATE_FORMAT(DATE_SUB(createTime),'%y%m%d') sdate FROM MyDataSheet group by sdate