一张表中有Id,Name,Age 三个字段
1、查询出name,age 重复的所有数据:
--查询数据库的重复记录(多列)
select a.*
from [dbo].[People] a
inner join (
select a.Name,a.Age
from [dbo].[People] a
group by a.Name,a.Age
having COUNT(*)>1
)b on a.Name=b.Name and a.Age=b.Age
2、删除数据库的重复的多余记录:
--删除数据库的重复的 [多余] 记录
--查询重复的记录 放入临时表 #ptable
select a.* into #ptable
from [dbo].[People] a
inner join (
select a.Name,a.Age
from [dbo].[People] a
group by a.Name,a.Age
having COUNT(*)>1
)b on a.Name=b.Name and a.Age=b.Age
--查询重复里面要保留的记录 ,放入临时表 #ptable2
select a.Name,a.Age,min(a.Id) otid into #ptable2
from #ptable a
group by a.Name,a.Age
--查询重复里面要删除的记录,删除
delete [dbo].[People]
where Id in (
select a.Id
from #ptable a
where a.Id not in (
select a.otid
from #ptable2 a
)
)
--删除临时表
drop table #ptable
drop table #ptable2