查询重复的行
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要是唯一的)
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要是唯一的)