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

￡神魔★判官ぃ 2023-10-17 01:28 1阅读 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');调用存储过程