根据年度、季度、月份查询统计数据

一：sql语句

select  date_format(sale_date, '%Y') as nian,count(id) as num from card group by nian;
select CONCAT(YEAR(sale_date),'-',quarter(sale_date)) jidu,count(id) as num from card group by jidu;
select  date_format(sale_date, '%Y-%m') as yue,count(id) as num from card group by yue;

二：结果

总结：mysql有好多函数用起来超级方便。

添加：具体到某天的按时间分

select count(card_state),shijian from (
select card_state, DATE_FORMAT( concat(date(sale_date),' ',hour(sale_date)) ,'%Y-%m-%d %H')  as shijian   
 from card WHERE DATE_FORMAT(sale_date,'%Y-%m-%d')='2020-11-23'  ORDER BY shijian
) a
group by  DATE_FORMAT(shijian,'%Y-%m-%d %H');