mysql获取报表数据的sql

以下例子：

表   trade

创建时间字段   gmt_create

一、按年统计

SELECT COUNT(*),YEAR(gmt_create) FROM trade  
GROUP BY YEAR(gmt_create) 

二、按月统计

SELECT COUNT(*),MONTH(gmt_create) FROM trade 
WHERE YEAR(gmt_create) = '2017' 
GROUP BY MONTH(gmt_create) 

三、按日统计

SELECT COUNT(*),DAY(gmt_create) FROM trade 
WHERE YEAR(gmt_create) = '2017' AND MONTH(gmt_create) = '5'
GROUP BY DAY(gmt_create)

四、按季度统计

QUARTER(gmt_create)可以获得这个时间属于的季度

参数为年，也可以指定季度QUARTER(gmt_create) = 1

SELECT QUARTER(gmt_create),gmt_create FROM trade 

SELECT COUNT(*),QUARTER(gmt_create) FROM trade 
WHERE YEAR(gmt_create) = '2017' 
GROUP BY QUARTER(gmt_create) 

五、按周统计

参数可以是某年的第几周，WEEK(gmt_create) ==？ 或者 WEEK(NOW())当前周数

SELECT COUNT(*),WEEK(gmt_create) FROM trade 
WHERE YEAR(gmt_create) = '2017' AND WEEK(gmt_create) = WEEK(NOW())
GROUP BY WEEK(gmt_create)

参数年，展示一年52个星期里的数据

SELECT COUNT(*),WEEK(gmt_create),gmt_create FROM trade 
WHERE YEAR(gmt_create) = '2017'
GROUP BY WEEK(gmt_create)