日期:2014-05-18  浏览次数:20562 次

这样的效果sql语句如何写呢?

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 454
go
--以大于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 all
select 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<=100
union all
select ltrim(id),col,rn from cte1 order by rn
/*
c            col         rn
------------ ----------- --------------------
●            55          1
1            101         2
2            152         3
3            154         4
4            287         5
●            14          6
1            871         7
2            451         8
3            570         9
●            75          10
●            -25         11
●            99          12
1            266         13
2            888         14
3            454         15

(15 行受影响)

*/
go
drop table tb