SQL语句去除重复项

 

SQL语句去除重复字段项

直接上代码

--测试数据库
Create table Test(
Id int primary key identity(1,1) not null,
name varchar(100) null,
age varchar(100) null
)
GO
--测试数据
insert into Test values('aa',18)
insert into Test values('aa',19)
insert into Test values('aa',20)
insert into Test values('bb',25)
insert into Test values('bb',20)
insert into Test values('cc',22)

--解析

--分组
SELECT name FROM Test
GROUP BY name
--出现次数
SELECT name,COUNT(name) AS 出现次数 FROM Test
GROUP BY name

--有重复的数据
SELECT NAME,COUNT(NAME) AS 重复数量 FROM TEST
GROUP BY name
HAVING COUNT(name)>1

--找出重复项中ID 最大的一个
SELECT NAME,COUNT(NAME) AS 重复数量,MAX(ID)AS 最大的ID FROM TEST
GROUP BY name
HAVING COUNT(name)>1

--找出重复项中ID 最小的一个
SELECT NAME,COUNT(NAME) AS 重复数量,MIN(ID)AS 最小的ID FROM TEST
GROUP BY name
HAVING COUNT(name)>1


--找出整个表中的重复数据；
SELECT * FROM TEST
WHERE name IN(
SELECT name FROM TEST
GROUP BY name
HAVING COUNT(name)>1
)

--删除所有 重复出现过的 项
 DELETE FROM TEST
 WHERE ID IN(
    SELECT ID FROM TEST WHERE name IN
    (
        SELECT name FROM TEST 
        GROUP BY name
        HAVING COUNT(name)>1
    )
 )

--删除重复项，保留重复项中的一个(通常用的是这个)

--第一步：找出重复项

--第二步：找出重复项中的最大id

--第三步： 删除 重复项中ID NOT IN (第二步)

   DELETE FROM TEST
   WHERE ID IN
   (
   SELECT ID FROM TEST WHERE name IN(SELECT name FROM TEST GROUP BY name HAVING COUNT(name)>1)
   AND
   ID NOT IN(SELECT MAX(ID) FROM TEST GROUP BY name HAVING COUNT(name)>1)
   )

 

  完美。