两种情况:
第一种 表不带ID,例子如下
表name
py mz
ganm 甘
gan 甘
gmk 甘
zheng 郑
zhe 郑
删除mz重复的部分,即要
py mz
ganm 甘
zheng 郑
sql代码如下:
select identity(int,1,1) as autoID, * into Tmp1 from name
select min(autoID) as autoId into Tmp2 from Tmp1 group by mz order by autoID
delete from name where py in (select py from Tmp1 where autoID not in (select autoId from Tmp2))
第二种情况,表带ID 例子如下:
表name
id py mz
1 ganm 甘
2 gan 甘
3 gmk 甘
4 zheng 郑
5 zhe 郑
删除mz重复的部分,即要
id py mz
1 ganm 甘
4 zheng 郑
sql代码如下:
方法一:delete from name where name.id not in (select min(id) from name group by mz)
方法二:delete from name where name.id not in(select min(id) from name as name1 where name.mz=name1.mz)