sqlserver如何删除rowguid字段不同其它字段相同的重复记录(100)

  • 主题发起人 主题发起人 98lh
  • 开始时间 开始时间
9

98lh

Unregistered / Unconfirmed
GUEST, unregistred user!
sqlserver如何删除rowguid字段不同其它字段相同的重复记录rowguid为自动生成如表a b c rowguid,1 100 25 XXXXXX11 100 25 XXXXXX21 100 25 XXXXXX3删除重复记录只保存其中条
 
Delete From T Where rowguid not in (Select MID From (Select Min(rowguid) as MID, Max(rowguid) as XID, a, b, c from T Where MID<>XID group by a, b, c) SubS1 Union Select MID From (Select Min(rowguid) as MID, Max(rowguid) as XID, a, b, c from T Where MID=XID group by a, b, c) SubS2)
 
如果你的rowguid是数字的时候先操作得到有相同的结果集,切这个结果集大于1,就像你上面说的那样a b c rowguid,1 100 25 11111111 100 25 11111121 100 25 1111113就直接删除 大于1111111 这个的就可以delete from TABLE where rowguid>111111 and a='1' and b='100' and c='25'
 
create table tmp (a, b, c, rowguid)select distinct a, b, c, rowguid() from tablenamedelete from tablenameinsert into tablename(a, b, c, rowguid) select a, b ,c, rowguid() from tmp自己调试一下,用一个临时表,这样清晰简单 select a, b, c from tablename group by a,b,c having count(1)>1这句,可以找出重复的
 
Delete From Table aWhere Exists (Select 1 from Table b where b.a=a.a and b.b=a.b and b.c=a.c and b.rowid < a.rowID)意思:如果存在abc三个字段和当前记录相同,且rowid小于当前记录的rowid的记录,则删除当前记录。
 

Similar threads

S
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
1K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
900
SUNSTONE的Delphi笔记
S
后退
顶部