MySQL算法案例1

建表：

create table test_tb(
    id int,
    name string
);

导入数据：

insert into test_tb values (1,'/'),(2,'A'),(3,'B'),
                           (4,'C'),(5,'/'),(6,'D'),
                           (7,'E'),(8,'/'),(9,'F'),
                           (10,'G'),(11,'H');

方法1：

select t1.id,t1.name,count(1) cnt
from test_db.test_tb t1
left join test_db.test_tb t2 on t1.id > t2.id and t2.name = '/'
where t1.name != '/'
group by t1.id,t1.name;

方法2：

with t1 as (select id,name,count(`if`(name='/',1,null)) over (order by id) cnt
from test_tb)
select id, name, t1.cnt
from  t1
where t1.name!='/';

方法3：

select *
from (
         select id, name,
                if(name = '/',@cn := @cn + 1,@cn) cnt
         from test_tb,(select @cn := 0) q
         ) t
where name != '/';