sql部分重复删除

两种情况：第一种　表不带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)