mysql 统计连续为正数出现的次数

select * from static_log;
select name,count(count_nu) from 
(select `name`,count_nu, case when count_nu >0 then (@c := @c + 1) end as zz, case when count_nu < 0 then (@b + @c) end  as cc from static_log,(select @b := 0) as Temp_a,(select @c := 1) as Temp_B ) as temp
where cc > 0 group by cc

使用了2个变量1个坐累加 当是负数的时候 一个变量做判断是否累加，最后分组

建表语句

CREATE TABLE `static_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `count_nu` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;

插入测试数据

INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(1,A,-10);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(2,A,-20);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(3,A,-30);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(4,A,10);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(5,B,-30);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(6,B,-10);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(7,B,-2);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(8,B,11);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(9,B,-3);
INSERT INTO `test`.`static_log` (`id`,`name`,`count_nu`)VALUES(10,B,-20);

全部数据结果

分组结果

select `name`,count_nu, case when count_nu >0 then (@c := @c + 1) end as zz, case when count_nu < 0 then (@b + @c) end  as cc from static_log,(select @b := 0) as Temp_a,(select @c := 1) as Temp_B 

之后根据分组结果做聚合即可
结果