数据库:ASE12.5
表:
ID COL
1 2434
2 454
3 342
4 67
5 [NULL]
6 [NULL]
7 23432
8 32
9 [NULL]
10 23
11 [NULL]
12 1232
13 [NULL]
14 [NULL]
15 [NULL]
1 2434
2 454
3 342
4 67
5 [NULL]
6 [NULL]
7 23432
8 32
9 [NULL]
10 23
11 [NULL]
12 1232
13 [NULL]
14 [NULL]
15 [NULL]
要求得到这个结果:
ID COL
4 67
5 [NULL]
6 [NULL]
8 32
9 [NULL]
10 23
11 [NULL]
12 1232
13 [NULL]
14 [NULL]
15 [NULL]
即空字段及它前面的那个非空字段保留
-----------------------------------
create table F(ID numeric(10,0) identity, COL int null)
go
insert into F values(2434)
insert into F values(454)
insert into F values(342)
insert into F values(67)
insert into F values(NULL)
insert into F values(NULL)
insert into F values(23432)
insert into F values(32)
insert into F values(NULL)
insert into F values(23)
insert into F values(NULL)
insert into F values(1232)
insert into F values(NULL)
insert into F values(NULL)
insert into F values(NULL)
go
go
insert into F values(2434)
insert into F values(454)
insert into F values(342)
insert into F values(67)
insert into F values(NULL)
insert into F values(NULL)
insert into F values(23432)
insert into F values(32)
insert into F values(NULL)
insert into F values(23)
insert into F values(NULL)
insert into F values(1232)
insert into F values(NULL)
insert into F values(NULL)
insert into F values(NULL)
go
select * into #tmp from F a where a.COL=NULL or exists (select 1 from F b where a.COL<>NULL and b.ID=a.ID+1 and b.COL=NULL)
select * from #tmp