sqlserver删除重复
① sqlserver删除重复记录
delete from friend where id in (select id from
(select id,userid, row_number() over(partition by userid order by id) as rn from friend ) as a where rn > 1)
② SQLserver数据库中所有字段全部一样的重复数据如何删除
找到最大的rowid即可。
Sql代码：
alterprocgetNotDupData
as
--cleartemptable
deleteODS.dbo.Agent
deletefromstage.dbo.tmpDup
deletefromstage.dbo.tmpRowNo
deletefromstage.dbo.tmpMaxRowNo
--createptable
insertintostage.dbo.tmpDup
selectdistinctAgentLogin,AgentSurName,AgentGivenNamefromstage.dbo.dAgentPerformanceStat
'3%'orderbyAgentLogin
--addrowNo
insertintotmpRowNo
select*,ROW_NUMBER()over(orderbyAgentLogin)asrownofromtmpDup
--getmaxrowno
insertintostage.dbo.tmpMaxRowNo
selectmax(rowno)as'rowno'fromstage.dbo.(*)>1
--removemaxrowno
deletefromstage.dbo.tmpRowNowhererownoin(select*fromstage.dbo.tmpMaxRowNo)
--insertintoods
insertintoODS.dbo.AgentselectAgentLogin,AgentSurName,AgentGivenNamefromstage.dbo.tmpRowNo
③ 菜鸟求教，sqlserver中删除重复数据的sql语句怎么写
--查出重复的数据，通过distinct去重，保存到临时表
select distinct * into #aaa from 表
where id in (select id from 表 group by having count(id) > 1)
--删除实表中的重复数据
delete from 表
where id in (select id from 表 group by having count(id) > 1)
--将删除掉的重复数据插入表中，保证表中只有一条，而没有重复
insert into 表(列)
select 列 from #aaa
--如果所有重复数据，一条都不需要保留，直接删除即可
④ sqlserver 去掉重复记录
首先设定表tb_a 唯一关键字段 xh,以及要查询的重复字段 mc 则查询mc重复的sqlserver语句如下
select mc from tb_a where xh not in (select min(xh) xh from tb_a group by mc)
⑤ sql中删除重复数据
SQL
Server删除重复行是我们最常见的操作之一，下面就为您介绍六种适合不同情况的SQL
Server删除重复行的方法，供您参考。
1.如果有ID字段，就是具有唯一性的字段
delect
table
where
id
not
in
(
select
max(id)
from
table
group
by
col1,col2,col3...
)
group
by
子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。
2.
如果是判断所有字段也可以这样
select
*
into
#aa
from
table
group
by
id1,id2,....
delete
table
insert
into
table
select
*
from
#aa
3.
没有ID的情况
select
identity(int,1,1)
as
id,*
into
#temp
from
tabel
delect
#
where
id
not
in
(
select
max(id)
from
#
group
by
col1,col2,col3...)
delect
table
inset
into
table(...)
select
.....
from
#temp
4.
col1+','+col2+','...col5
联合主键
select
*
from
table
where
col1+','+col2+','...col5
in
(
select
max(col1+','+col2+','...col5)
from
table
where
having
count(*)>1
group
by
col1,col2,col3,col4
)
group
by
子句后跟的字段就是你用来判断重复的条件，如只有col1，那么只要col1字段内容相同即表示记录相同。
5.
select
identity(int,1,1)
as
id,*
into
#temp
from
tabel
select
*
from
#temp
where
id
in
(
select
max(id)
from
#emp
where
having
count(*)>1
group
by
col1,col2,col3...)
6.
select
distinct
*
into
#temp
from
tablename
delete
tablename
go
insert
tablename
select
*
from
#temp
Sqlclub
go
drop
table
#temp
以上就是SQL
Server删除重复行的方法介绍。
⑥ SQL Server 2000 删除重复记录问题
你可以重新建立一个新表，然后将原表中的信息不重复的选出来，复制到新表中，再将原表删除，就ok了。
⑦ sqlserver怎么删除重复数据
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select
* from people
where peopleId in (select peopleId from
people group by peopleId having count(peopleId)
> 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete
from people
where peopleId in (select peopleId from
people group by peopleId having
count(peopleId) > 1)
and rowid not in (select min(rowid) from
people group by peopleId having count(peopleId
)>1)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq)
in (select peopleId,seq from vitae group by peopleId,seq having
count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from
vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where
(a.peopleId,a.seq) in (select peopleId,seq from vitae group by
peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from
vitae group by peopleId,seq having count(*)>1)
(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select
Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having
Count(*) > 1
⑧ sqlserver里面怎么删除重复的记录
新建一个表,然后用
select distinct id,其它字段 into newtable FROM oldtable
旧表就扔了