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)