DELETE数据的查询

[xiaomuwing]

表结构：

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]


要求删除完了得到这个结果：
ID COL
4 67
5 [NULL]
6 [NULL]
8 32
9 [NULL]
10 23
11 [NULL]
12 1232
13 [NULL]
14 [NULL]
15 [NULL]

也就是说空字段以及最前面的那个非空字段保留，其他删除

靠！我又用游标了…………是这么写的：

declare CUR_ID cursor for
select ID，COL from TABLE1 order by ID
open CUR_ID

fetch next from CUR_ID into @ID,@COL
while @@FETCH_STATUS = 0
begin
fetch next from CUR_ID into @ID,@COL
if @COL is not null
begin
if exists(select 1 from TABLE1 where ID = @ID -1 and COL is not null)
delete from TABLE1 where ID = @ID -1
end
end
close CUR_ID
deallocate CUR_ID

这个效率实在不咋地……有没有好办法？

[huangxinru]

用datawindow试试

[huangxinru]

sorry!又发错地方了

[lw1a2]

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

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

[feng1071]

drop table a
declare @a table(ID int, col int)
insert into @a select 1,2434
insert into @a select 2,454
insert into @a select 3,342
insert into @a select 4,67
insert into @a select 5,null
insert into @a select 6,null
insert into @a select 7,23432
insert into @a select 8,32
insert into @a select 9,null
insert into @a select 10,23
insert into @a select 11,null
insert into @a select 12,1232
insert into @a select 13,null
insert into @a select 14,null
insert into @a select 15,null




create table a(id int,col int,sno int)
insert into a(id,col)
select id,col
from @a

update a
set a.sno=1

select distinct d.id,d.col
from
(select b.y x
from
(select b.id,b.col x,c.id y,c.col z
from a b left join a c
on (select max(id) from a c where b.id>c.id and b.sno=c.sno)=c.id)b
where b.x is null and b.z is not null)b,(select id from a where col is null)c,@a d
where b.x=d.id or c.id=d.id

[feng1071]

drop table a
declare @a table(ID int, col int)
insert into @a select 1,2434
insert into @a select 2,454
insert into @a select 3,342
insert into @a select 4,67
insert into @a select 5,null
insert into @a select 6,null
insert into @a select 7,23432
insert into @a select 8,32
insert into @a select 9,null
insert into @a select 10,23
insert into @a select 11,null
insert into @a select 12,1232
insert into @a select 13,null
insert into @a select 14,null
insert into @a select 15,null




create table a(id int,col int,sno int)
insert into a(id,col)
select id,col
from @a

update a
set a.sno=1

delete c
from @a c
where c.id not in(
select d.id
from
(select b.y x
from
(select b.id,b.col x,c.id y,c.col z
from a b left join a c
on (select max(id) from a c where b.id>c.id and b.sno=c.sno)=c.id)b
where b.x is null and b.z is not null)b,(select id from a where col is null)c,@a d
where b.x=d.id or c.id=d.id)

select* from @a

[yongwin]

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
delete a from f a where a.id not in
(select a.id from f a,
(select id from f where col is null)b
where b.id-1=a.id or a.id=b.id)

select * from f
drop table f