這幾個字段都可能有完全相同的記錄,我隻要保留其中的隨便一條,請問怎麼寫
如果还有一个能区分大小(這幾個字段除外)id1
delete from tb where id1 not in (select min(id1) from tb group by 這幾個字段)
如果没有一个能区分大小,则只能使用临时表.
select id2 = identity(int,1,1) , * into temp from tb
delete from tb where id2 not in (select min(id2) from tb group by 這幾個字段)
---保留相同Name,id最大那行
Delete A From Test1 As A Where Exists
(Select 1 From Test1 Where Name=A.Name And id>A.id)
---保留相同Name,id最小那行
Delete A From Test1 As A Where Exists
(Select 1 From Test1 Where Name=A.Name And id<A.id)
---只要Name有重复的,就将其全部删除
Delete A From Test1 As A Where Exists
(Select 1 From Test1 Where Name=A.Name Group By Name Having Count(1)>1)
---删除重复,保留相同Name,id最大那行
Delete A From @Test1 As A Where Exists
(Select 1 From @Test1 Where Name=A.Name And id>A.id)
---删除重复,保留相同Name,id最小那行
Delete A From @Test1 As A Where Exists
(Select 1 From @Test1 Where Name=A.Name And id<A.id)