请问关于删除数据库里主键不同,其他字段相同的SQL语句
请教关于删除数据库里主键不同,其他字段相同的SQL语句!
数据库里有一个表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语句,删除其中的两条,只留下一条。
数据库里这样的数据很多,几百行吧。
求救!!!请大家帮忙!!!先谢谢了
------解决方案--------------------
数据库里有一个表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语句,删除其中的两条,只留下一条。
数据库里这样的数据很多,几百行吧。
求救!!!请大家帮忙!!!先谢谢了
------解决方案--------------------
- SQL code
delete t from tb t where exists(select 1 from tb where DocumentID=DocumentID and DocumentName=t.DocumentName and id<t.id)
------解决方案--------------------
- SQL code
delete from document where not exsits (select max(id),DocumentID,DocumentName from document )
------解决方案--------------------
- SQL code
with cte as (select *,row_number()over(partition by DocumentID,DocumentName order by getdate())as RN from tb) delete from cte where RN>1
------解决方案--------------------
- SQL code
---------------------------- -- 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 行受影响) */
------解决方案--------------------
- SQL code
create table document(ID varchar(50),DocumentID int,DocumentName varchar(10)) 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
------解决方案--------------------
- SQL code
delete from document where not exsits (select max(id),DocumentID,DocumentName from document where DocumentID=DocumentID and DocumentName=DocumentName )