利用sql语句去重

8870188

查询重复的行

select hospital_name,hospital_address from gz_hospital group by hospital_name,hospital_address having count(hospital_name)>1



sql去重复的行

情形一

（已验证）

DB:SQL SERVER

行的数据完全重复

采用下面的步骤可清除重复的数据，只保留一行

select distinct * into #Tmp from tes1
truncate  table tes1
select * into tes1 from #Tmp
drop table #Tmp

tes1为你的表名
#Tmp，这是个临时表，不需要变，你也不需要建，执行就可以



情形二、

（未验证）

如果有唯一标识列

delete table
from table a
where exists(select 1 from table where a.name = name and a.pwd = pwd and a.id < id)



情形三

DB: SQL SERVER

（已验证）

根据某一个或几个字段判断去重复
select identity(int,1,1) as autoID, * into #Tmp from tes1
select min(autoID) as autoID into #Tmp2 from #Tmp group by name
drop table tes1
select name,address,tel into tes1 from #Tmp where autoID in(select autoID from #tmp2)

这里的name,address,tel是表里的所有有用的字段，如果不选，则字段为空
这里的name就是你判断去重复的字段


delete from gz_hospital a where id>(
select min(id) from gz_hospital b
group by name,address)
(未验证)

delete from gz_hospital a where id not in(
select min(id) from gz_hospital b
group by name,address)
(验证，id要是唯一的)