数据库里有一个表document,
这个表有三个字段ID(主键),DocumentID,DocumentName
假如这个表里有三条数据
第一条8941ada0-bc47-4ee3-9bae-5dd660bc75e5,1,doc1
第二条bc53ec55-f776-4f88-91e5-827e1d9595a8,1,doc1
第三条547d2b1a-3233-41dd-8578-d3a25f3ef911,1,doc1那么我怎么写SQL语句,删除其中的两条,只留下一条。
数据库里这样的数据很多,几百行吧。
求救!!!请大家帮忙!!!先谢谢了
这个表有三个字段ID(主键),DocumentID,DocumentName
假如这个表里有三条数据
第一条8941ada0-bc47-4ee3-9bae-5dd660bc75e5,1,doc1
第二条bc53ec55-f776-4f88-91e5-827e1d9595a8,1,doc1
第三条547d2b1a-3233-41dd-8578-d3a25f3ef911,1,doc1那么我怎么写SQL语句,删除其中的两条,只留下一条。
数据库里这样的数据很多,几百行吧。
求救!!!请大家帮忙!!!先谢谢了
from document )
with cte as
(select *,row_number()over(partition by DocumentID,DocumentName order by getdate())as RN from tb)
delete from cte where RN>1
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-03 16:15:46
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] uniqueidentifier,[DocumentID] int,[DocumentName] varchar(4))
insert [tb]
select '8941ada0-bc47-4ee3-9bae-5dd660bc75e5',1,'doc1' union all
select 'bc53ec55-f776-4f88-91e5-827e1d9595a8',1,'doc1' union all
select '547d2b1a-3233-41dd-8578-d3a25f3ef911',1,'doc1'
--------------开始查询--------------------------
delete t from tb t where exists(select 1 from tb where DocumentID=DocumentID and DocumentName=t.DocumentName and id<t.id)select * from tb
----------------结果----------------------------
/* ID DocumentID DocumentName
------------------------------------ ----------- ------------
8941ADA0-BC47-4EE3-9BAE-5DD660BC75E5 1 doc1(1 行受影响)
*/
insert into document select '8941ada0-bc47-4ee3-9bae-5dd660bc75e5',1,'doc1'
insert into document select 'bc53ec55-f776-4f88-91e5-827e1d9595a8',1,'doc1'
insert into document select '547d2b1a-3233-41dd-8578-d3a25f3ef911',1,'doc1'
go
delete t from document t
where exists(select 1 from document where DocumentID=t.DocumentID and DocumentName=t.DocumentName and ID>t.id)
select * from document
/*
ID DocumentID DocumentName
-------------------------------------------------- ----------- ------------
bc53ec55-f776-4f88-91e5-827e1d9595a8 1 doc1(1 行受影响)
*/
go
drop table document
from document where DocumentID=DocumentID and DocumentName=DocumentName )