sqlserver查询重复数据，删除并保留一条。

或与且与或非

已于 2024-01-09 17:27:57 修改

阅读量5.1k

点赞数 1

分类专栏： sqlserver 文章标签： sql sqlserver

于 2020-12-24 09:07:21 首次发布

本文链接：https://blog.csdn.net/qq_42455262/article/details/111603736

版权

sqlserver 专栏收录该内容

13 篇文章 2 订阅

订阅专栏

本文介绍了在SQL Server中如何查找并删除重复数据。首先，通过创建临时表存储重复记录，然后使用GROUP BY和HAVING子句筛选出重复项。接着，利用NOT IN操作符从原始表中删除重复数据，确保每组重复数据只保留一条记录。这种方法适用于多字段的查重和删除，确保数据的唯一性。

摘要由CSDN通过智能技术生成

简单版本

  select max(id) as id,count(DslSku) as 重复数量,DslSku into #temp from ProductCheck_Code group by DslSku having(DslSku)>1

  select * from  ProductCheck_Code where id not in(select id From #temp) 
  drop table #temp

多字段查重

·先存入临时表再进入重复数据查询·

 select  dy.productId,dy.goodsNo,dy.skuid,sc.DouYinStoreNo,sit.goodsqty,sit.placepointid  
  into #temp from lswx.dbo.MedicineInfo_DouYin dy   left join dsl.dbo.Store_inventory_table  sit  
  on dy.goodsNo=sit.DslGoodsId left join dsl.dbo.StoreCheck_Code sc on sc.DslStoreNo=sit.DslStoreId 
  and sc.DouYinStoreNo<>'-' where sit.isChange_DouYin=1 and sc.DouYinStoreNo is not null

SELECT a.* FROM #temp a,
(
SELECT productId,placepointid FROM #temp
GROUP BY placepointid,productId
HAVING COUNT(1)>1
) AS b
WHERE a.productId=b.productId AND a.placepointid=b.placepointid

drop table #temp

查询重复数据

	  select *  from SHSJ0101 where  XSDM in (select XSDM from SHSJ0101 where SHSJPCDM = 'b1f492d67da743dc838e214195a2a524'  group by XSDM having count(XSDM) > 1) and SQSJ not in(select  max(SQSJ) from SHSJ0101 where SHSJPCDM = 'b1f492d67da743dc838e214195a2a524' group by  XSDM having count(XSDM) > 1 )and SHSJPCDM = 'b1f492d67da743dc838e214195a2a524'

删除重复数据只保留一条

delete from SHSJ0101 where  XSDM in (select XSDM from SHSJ0101 where SHSJPCDM = 'b1f492d67da743dc838e214195a2a524'  group by XSDM having count(XSDM) > 1) and SHSJPCDM = 'b1f492d67da743dc838e214195a2a524'  and SQSJ not in(select  max(SQSJ) from SHSJ0101 where SHSJPCDM = 'b1f492d67da743dc838e214195a2a524' group by  XSDM having count(XSDM) > 1 )