MSSQL2008删除表列重复行（保留一行）

方法1

简单粗暴直接删除

delete 表名 where id not in (select min(id) from 表名 group by 列名)

方法2
先将所有的重复数据显示出来：
SELECT 列名

FROM  表名

WHERE (列名 IN (SELECT 列名 FROM 表名 GROUP BY 列名 HAVING (COUNT(列名) > 1)))

删除多余的记录
delete from 表名

WHERE (列名 IN (SELECT 列名 FROM 表名 GROUP BY 列名 HAVING (COUNT(列名) > 1)))
AND  (id NOT IN (SELECT MIN(id) AS nid FROM 表名 GROUP BY 列名 HAVING (COUNT(列名) > 1)))

－－－－－－－－－－－－－－－－－－－－－－

自用事例

删除重订单号
方法1
delete DomainBuy where id not in (select min(id) from DomainBuy group by ordersid)

方法2
先将所有的重复数据显示出来：
SELECT ordersid
FROM  DomainBuy
WHERE (ordersid IN (SELECT ordersid FROM DomainBuy GROUP BY ordersid HAVING (COUNT(ordersid) > 1)))

删除多余的记录
delete from DomainBuy
WHERE (ordersid IN (SELECT ordersid FROM DomainBuy GROUP BY ordersid HAVING (COUNT(ordersid) > 1)))
AND  (id NOT IN (SELECT MIN(id) AS nid FROM DomainBuy GROUP BY ordersid HAVING (COUNT(ordersid) > 1)))

把多余的重复记录重置为0
update DomainBuy set ordersid=0
WHERE (ordersid IN (SELECT ordersid FROM DomainBuy GROUP BY ordersid HAVING (COUNT(ordersid) > 1)))
AND  (id NOT IN (SELECT MIN(id) AS nid FROM DomainBuy GROUP BY ordersid HAVING (COUNT(ordersid) > 1)))

---------------------------------------
sqlserver2008 还原数据库出错3101

ALTER DATABASE [数据库名] SET OFFLINE WITH ROLLBACK IMMEDIATE

alter database [数据库名] set online

最快处理

UPDATE DomainBuy 

SET ordersid = 0

WHERE (ordersid IN (

    SELECT ordersid 

    FROM DomainBuy 

    GROUP BY ordersid 

    HAVING COUNT(ordersid) > 1

))

AND (id NOT IN (

    SELECT MIN(id) 

    FROM DomainBuy 

    GROUP BY ordersid 

    HAVING COUNT(ordersid) > 1

));