两种情况:第一种 表不带ID,例子如下
表namepy mzganm 甘gan 甘gmk 甘zheng 郑zhe 郑
删除mz重复的部分,即要
py mzganm 甘zheng 郑
sql代码如下:
select identity(int,1,1) as autoID, * into Tmp1 from nameselect min(autoID) as autoId into Tmp2 from Tmp1 group by mz order by autoIDdelete from name where py in (select py from Tmp1 where autoID not in (select autoId from Tmp2))
第二种情况,表带ID 例子如下:
表nameid py mz1 ganm 甘2 gan 甘3 gmk 甘4 zheng 郑5 zhe 郑
删除mz重复的部分,即要id py mz1 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)