mysql 按年月日分组统计

 create_time timestamp格式（如：2016-06-28 11:03:28）

        SELECT DATE_FORMAT(create_time,'%Y%u')  AS  weeks,COUNT(id)   AS  count FROM role GROUP BY weeks;

        SELECT DATE_FORMAT(create_time,'%Y%m%d')   AS  days,COUNT(id)   AS  count FROM role GROUP BY days; 

        SELECT DATE_FORMAT(create_time,'%Y%m')   AS  months,COUNT(id)   AS  count  FROM role GROUP BY months 

 

create_time时间戳格式（如：1467083014）

        SELECT FROM_UNIXTIME(create_time,'%Y%u')   AS  weeks,COUNT(id)   AS  count  FROM role GROUP BY weeks; 

        SELECT FROM_UNIXTIME(create_time,'%Y%m%d')  AS   days,COUNT(id)   AS  count  FROM role GROUP BY days; 

        SELECT FROM_UNIXTIME(create_time,'%Y%m')   AS  months,COUNT(id)   AS  count  FROM role GROUP BY months