数据库高手请绕过。
题目我没想好,说一下更容易明白。想想答案很简单,但是我走了弯路。
有两个结构一样的表:content as c, content_temp as t.主键为ID
t表中含有部分c表数据,以title和answer同时相同为条件确定此数据是否同时存在于两表中,若存在,则删除t表中此条数据。
第一反应构建的是这样的语句:
delete from content_temp t where t.title in (select title from content ) and t.answer in (select answer from content)
仔细一看就知道不行了,in 条件只能有一个。两个in数据库都不知道你要做什么。
后来这样做了,用游标循环,可以实现效果
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
1
declare
@title
nvarchar
(
max
);
2 declare @answer nvarchar ( max );
3
4 declare myCursor cursor for
5 select title,answer from content;
6
7 open myCursor;
8 fetch next from myCursor
9 into @title , @answer ;
10
11 while @@FETCH_STATUS = 0
12 begin
13 print @title ;
14 delete from content_temp
15 where title = @title and answer = @answer
16 fetch next from myCursor
17 into @title , @answer ;
18
19 end
20
21 close myCursor;
22 deallocate myCursor
2 declare @answer nvarchar ( max );
3
4 declare myCursor cursor for
5 select title,answer from content;
6
7 open myCursor;
8 fetch next from myCursor
9 into @title , @answer ;
10
11 while @@FETCH_STATUS = 0
12 begin
13 print @title ;
14 delete from content_temp
15 where title = @title and answer = @answer
16 fetch next from myCursor
17 into @title , @answer ;
18
19 end
20
21 close myCursor;
22 deallocate myCursor
23 go
这样做的结果是速度非常慢,尤其是大数据量,我这个是30万的数据,执行五分钟我就放弃了,估计最少要一个小时能执行完,因为这种做法循环太多。
最终的解决办法是:
delete from content_temp
where id in (
select t.id from content_temp t
inner join content c on t.title=c.title and t.answer=c.answer)
为什么这么简单的问题一定要绕个弯才能想到呢? 数据库高手请绕过。
PS:博客园的代码不显示bug什么时候能解决?