●
1
2
3
4
●
1
2
3
●
●
●
1
2
3
满足条件的显示●，不满足条件的显示 1 2 3 4 .....
------解决方案--------------------
- SQL code
create table tb(col int)insert into tb select 55 union all select 101 union all select 152 union all select 154 union all select 287 union all select 14 union all select 871 union all select 451 union all select 570 union all select 75 union all select -25 union all select 99 union all select 266 union all select 888 union all select 454go--以大于100为条件:;with cte as(select row_number()over(order by (select 1))rn,col from tb),cte1 as(select 1 as id,* from cte a where col>100 and exists(select 1 from cte where rn=a.rn-1 and col<=100)union allselect a.id+1,b.* from cte1 a inner join cte b on a.rn=b.rn-1 and b.col>100)select '●' as c,col,rn from cte where col<=100union allselect ltrim(id),col,rn from cte1 order by rn/*c col rn------------ ----------- --------------------● 55 11 101 22 152 33 154 44 287 5● 14 61 871 72 451 83 570 9● 75 10● -25 11● 99 121 266 132 888 143 454 15(15 行受影响)*/godrop table tb