欢迎光临众联科技,开始互联网之旅! 请〖登录〗 〖注册〗
MSSQL2008 删除表列重复行方法(保留一行)
发布时间:2020/7/7 14:51:57   发布来源:众联科技

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

(责任编辑:众联科技)
微信公众号
微信公众号 关注有礼
  • 服务支持
  •