mysql 日、周、月、年

1、统计出某年新增人数

select date_format(creat_at , '%Y') as year  , sum(num) as num from 表名 ount group by date_format(creat_at,'%Y');

select year(creat_at) as year  , sum(num) as num from 表名 ount group by year(creat_at);

2、统计出每年每月新增人数

select date_format(creat_at , '%Y-%m') as month , sum(num) as num from 表名 group by date_format(creat_at,'%Y-%m');

3、统计出今年每月的新增人数

select date_format(creat_at , '%Y-%m') as month , sum(num) as num from 表名 where year(creat_at) = year(curdate())-1   group by date_format(creat_at,'%Y-%m'));

4、得到某月销卡最多的俱乐部

select clube_id name, clube_cards value from 表名 where  month(creat_at) = month(now()) order by value desc

5、得到某年销卡最多的俱乐

select clube_id name, sum(clube_cards) value from 表名 where  year(creat_at) = year(now())  group by name  order by value desc;