faunu 2021-04-12 09:59 采纳率: 100%
浏览 180
已采纳

MySQL存储过程求连续天数

求ID对应DAYS字段的连续天数、连续次数、累计天数

#天数只有本月内,不跨月,不跨年

#连续天数取最后一次连续天数

#连续次数取连续3天算一次,每增加连续1天加1,如果连续天数为4天,连续次数就是1+1,连续天数为5天,连续次数就是1+2

#如果连续天数被打断,则连续打断,重新开始计算连续异常次数

  • 写回答

6条回答 默认 最新

  • CSDN专家-孙老师 2021-04-12 17:04
    关注

    这个问题要分两步来考虑:

    第一步:将日期字符串切分开,并和对应的ID另存为一张表。

    第二步:从第一步得到表中,查询各ID的对应DAYS字段的连续天数、连续次数、累计天数。

    原始表的数据如下图所示:

    第一步:

    1、建表,用于存放处理好的字期和对应ID

    create table days_tb(
        ID varchar(5),
        DAYS date
    );

    2、定义存储过程,用于将字符串日期转化为行的形式,并和对应ID存入表中

    delimiter //
    create procedure sub_str(id varchar(5),days_item varchar(100))
    begin
    	insert into days_tb(id,days) SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(days_item,',',help_topic_id+1),',',-1) AS num
    	FROM mysql.help_topic
    	WHERE help_topic_id < LENGTH(days_item)-LENGTH(REPLACE(days_item,',',''))+1;
    end //
    delimiter ;

    3、定义存储过程,利用游标,将所有的记录都进行第2步的处理

    delimiter //
    create procedure days_pro()
    begin
        declare done int default 0;
        declare days_item varchar(100);
        declare id_item varchar(5);
        declare days_cursor cursor for select ID,DAYS from item_tb;
        declare continue handler for not FOUND set done = 1; /*done = true;亦可*/
        open days_cursor;
        repeat
            fetch days_cursor into id_item,days_item;
    	    if not done then
                call sub_str(id_item,days_item);
    	    end if;
        until done end repeat;
        close days_cursor;
    end //
    delimiter ;

    处理完成后,此时原始表中的数据被转换到新表days_tb中,格式如下:

    第二步:

    从新表days_tb中查询各ID的对应DAYS字段的连续天数、连续次数、累计天数。

    select t5.ID,t5.days1,if(t5.days1>=3,t5.days1-2,0) as con_count,t7.cumsum_days from(
    	select ID,start_date,count(count_day) as days1,sum(t4.count_day) as cum_days from(
    	select ID,
    		@start_day:=DAYS as start_date,
    		@count_day:=
    			(case
    				when (@ID:=ID and datediff(DAYS, @end_date)=1) then (@count_day+0)
    				when (@ID:=ID and datediff(DAYS, @end_date)<1) then (@count_day+0)
    				else (@count_day+1)
    				end) as count_day,            
    		@ID:=ID,
    		@end_date:=DAYS
    	from (select ID,DAYS from days_tb order by ID) as t2,
    		(select @ID:='',@start_date:='',@end_date:='',@count_day:=0) as t3) as t4
    	group by ID,count_day) as t5,
        (select ID,max(start_date) as date_max,sum(days1) as cumsum_days from(
    		select ID,start_date,count(count_day) as days1 from(
    			select ID,
    				@start_day:=DAYS as start_date,
    				@count_day:=
    					(case
    						when (@ID:=ID and datediff(DAYS, @end_date)=1) then (@count_day+0)
    						when (@ID:=ID and datediff(DAYS, @end_date)<1) then (@count_day+0)
    							else (@count_day+1)
    						end) as count_day,            
    				@ID:=ID,
    				@end_date:=DAYS
    		from (select ID,DAYS from days_tb order by ID) as t2,
    			(select @ID:='',@start_date:='',@end_date:='',@count_day:=0) as t3) as t4
    		group by ID,count_day)as t6 group by ID)as t7
    		where t5.start_date=t7.date_max and t5.ID=t7.ID;

    格式有点乱,大概就是这样。

    查询结果如下:

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(5条)

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀