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

无情 阅读:213 2021-03-31 21:49:30 评论:0

存储过程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 where  1=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 where  1=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 where  1=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 where  1=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 where  1=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 where  1=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');调用存储过程


声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
KIKK导航

KIKK导航

关注我们