DELETE数据的查询

2005-12-05 02:55:01 210 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

生产数据恢复系列之使用闪回查询恢复Oracle delete误删数据使用闪回查询恢复delete误删数据阅读详情

表结构：

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

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

...全文

使用limit分页查询时,做delete操作,会导致丢失查找数据一、准备数据 1.1 mysql数据脚本 1.2代码二、验证 1.验证前全部数据 2.第一页数据 3.删除记录 4. 新增记录 5.第二页数据 6. 验证后的数据 7. log 三、结论四、建议没看过瘾?想要源文件? 一、准备数据 1.1 mysql数据脚本 ... 阅读详情

xiaomuwing

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

yongwin 2005-12-05

打赏
举报

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

feng1071 2005-12-05

打赏
举报

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

feng1071 2005-12-05

打赏
举报

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

lw1a2 2005-12-05

打赏
举报

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

huangxinru 2005-12-05

打赏
举报

sorry!又发错地方了

huangxinru 2005-12-05

打赏
举报

用datawindow试试

DELETE数据的查询

相关推荐