SQL语法--获取统计数据

近期在写统计，就把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