MySQL 基于存储过程实现数据统计按日、周、月份统计模板 MySQL

存储过程developer_count 是根据传入参数searchType 决定是使用那种查询方式，本存储过程中包含的其他的参数是{起始时间：startime,结束时间：endtime}

CREATE PROCEDURE developer_count ( searchType int, startTime varchar(64), endTime varchar(64) ) BEGIN /*定义变量天数*/ declare day_num int; if searchType = 1 then /*本周数据查询*/ select count(d.acct_id),d.acct_old_time from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(d.acct_old_time) GROUP BY d.acct_old_time; end if; if searchType = 2 then /*本月数据查询*/ select count(d.acct_id),d.acct_old_time from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(d.acct_old_time) GROUP BY d.acct_old_time; end if; if searchType = 3 then /*最近三个月数据查询*/ select count(d.acct_id), DATE_FORMAT(d.acct_old_time,'%x年-第%v周') as weeks from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= date(d.acct_old_time) GROUP BY weeks; end if; if searchType = 4 then /*按月份进行数据统计*/ select datediff(startTime, endTime) into day_num; if day_num <=7 then select count(d.acct_id),d.acct_old_time from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL day_num DAY) <= date(d.acct_old_time) GROUP BY d.acct_old_time; end if; if day_num >7&& day_num <= 30 then select count(d.acct_id),d.acct_old_time from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL day_num DAY) <= date(d.acct_old_time) GROUP BY d.acct_old_time; end if; if day_num >30 && day_num <= 90 then select count(d.acct_id), DATE_FORMAT(d.acct_old_time,'%x年-第%v周') as weeks from developer d where1=1 and DATE_SUB(CURDATE(), INTERVAL day_num DAY) <= date(d.acct_old_time) GROUP BY weeks; end if; end if; end;

调用存储过程方法
CALL DEVELOPER_COUNT(1,'2016-06-07','2016-06-16'); 调用存储过程

【MySQL 基于存储过程实现数据统计按日、周、月份统计模板】