删除重复数据
本帖最后由 xiongxing318 于 2012-11-14 15:48:09 编辑 有表AddressBookLinkMan 字段LinkManId AddressBookId
没主键，我要查询出2个字段完全重复的数据，并且删除。只留下一条！！
LinkManId AddressBookId
a1 w
a1 w
b1 c
如上第一条跟第二条完全重复，我要删掉一条留一条sql怎么写？？不使用临时表，以及加临时字段。就一条sql语句能解决吗？？？？
[最优解释]
delete a from (select *,
row_number() over(partition by LinkManId,AddressBookId order by LinkManId) no from AddressBookLinkMan ) a
where a.no>1
[其他解释]

--临时表
select distinct * into #Tmp from AddressBookLinkMan
drop table AddressBookLinkMan
select * into AddressBookLinkMan from #Tmp
drop table #Tmp

如果你添加的唯一列，就可以按照这种方式删除了：
http://bbs.csdn.net/topics/240034273
[其他解释]
在线等啊。如果没法子就只能用临时表或者临时字段了
[其他解释]
表竟然没有主键，我也没有其他好办法了。
[其他解释]

引用:

SQL code?123456--临时表select distinct * into #Tmp from AddressBookLinkMandrop table AddressBookLinkManselect * into AddressBookLinkMan from #Tmpdrop table #Tmp

如果你添加的唯一列，就可以按照这种方式删除了：
h……

蛋疼的就是没主键啊。。 真的没其他法子了么。。
[其他解释]
SQL SERVER 2012支持 with表达式的删除！

declare @T TABLE
([LinkManId] varchar(2),[AddressBookId] varchar(1))
insert @T
select 'a1','w' union all
select 'a1','w' union all
select 'b1','c'

;with maco as 
(
select 
row_number() over (partition by [LinkManId],[AddressBookId] order by (select 1) ) as id,
* 
from @T
)
delete from maco where id<>1 

select * from @T
/*
LinkManId AddressBookId
--------- -------------
a1        w
b1        c

(2 行受影响)
*/

[其他解释]
create table #A(id int)
insert into #A
select 1
union all
select 1
union all
select 2
union all
select 2
------测试
with TB as (
select *,ROW_NUMBER()over(partition by id order by id) as rowid

from #A)

delete from TB where rowid>1
[其他解释]

引用:

delete a from (select *,
row_number() over(partition by LinkManId,AddressBookId order by LinkManId) no from AddressBookLinkMan ) a
where a.no>1

这个方法不错。