Sqlserver根据某个字段查询重复字段并删除

菜单

一、根据某个字段查看重复的记录
6、掌握exists和not exists

一、根据某个字段查看重复的记录

1、数据准备：插入临时表

SELECT a.* into #XinTest from (
select 'Fox' Name,'男' Sex,1 as Class,80 FScore, 'Chinese'  as sub union 
select 'Xing' Name,'男' Sex,1 as Class,60 FScore,'Chinese' as sub union 
SELECT 'EN' Name,'女' Sex,2 as Class,55 FScore,'Chinese' as sub union 
select 'EN' Name,'女' Sex,2 as Class,56 FScore,'English' as sub union
select 'EN' Name,'女' Sex,2 as Class,57 FScore,'Maths' as sub union 
select 'EN' Name,'女' Sex,1 as Class,69 FScore,'Chinese' as sub union 
select 'EN' Name,'女' Sex,1 as Class,76 FScore,'English' as sub union
select 'EN' Name,'女' Sex,1 as Class,95 FScore,'Maths' as sub union 
select 'May' Name,'女' Sex,2 as Class,89 FScore,'Chinese' as sub union 
select 'May' Name,'女' Sex,2 as Class,96 FScore,'English' as sub union
select 'May' Name,'女' Sex,2 as Class,92 FScore,'Maths' as sub
) a where 1=1

2、根据某个字段查看重复记录

SELECT Name,
       COUNT(*) AS count
FROM #XinTest
GROUP BY Name
HAVING COUNT(*) > 1;

3、根据某两个字段查看重复记录

SELECT Name,
       Class,
       COUNT(*) AS count
FROM #XinTest
GROUP BY Name,
         Class
HAVING COUNT(*) > 1;

4、给重复的记录进行排序

给重复的记录进行排序，以名字为分组分数倒叙（可使用在根据某些字段【PARTITION BY】来取最大或最少值【order by】）

SELECT ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name, FScore DESC) AS row_id,
       *
FROM #XinTest;

5、删除重复记录但保留某个字段最大值

方法一：临时表

3.1、添加rowid 
select ROW_NUMBER() over(order by Name,FScore) as row_id,* into #XinTestNew from #XinTest
3.2 查询某个字段并rowid最小
select min(row_id) from #XinTestNew
group by name having count(name)>1
3.3、删除
select * 
--delete
from #XinTestNew
where name in(
select name from #XinTestNew group by name having count(name)>1
) and row_id not in(
select min(row_id) from #XinTestNew
group by name having count(name)>1
)

方法二递归删除

WITH cte AS (
    SELECT name, sex,class, FScore,
           ROW_NUMBER() OVER (PARTITION BY name,sex, class ORDER BY FScore desc) AS rn
    FROM #XinTest
)
--SELECT * FROM cte
DELETE FROM cte
WHERE rn > 1;

6、掌握exists和not exists

exists （sql 返回匹配条件的结果集）
not exists （ sql 返回没匹配条件的结果集)

例如我现在有两个表A和表B

ID	Name
1	A1
2	A2
3	A3

表A

ID	AID	Name
1	1	B
2	2	B2
3	2	B3

表B

表A和表B的关联关系是：表B的AID=表A的ID，通过上图得出是1对多的关系。

我们通过例子看看”exists”和”not exists”的区别

SELECT ID,
       NAME
FROM A
WHERE EXISTS
(
    SELECT * FROM B WHERE A.ID = B.AID
);

执行结果为

ID	Name
1	A1
2	A2

EXISTS的结果集

我们经常可以使用EXISTS来替代in来查看是否存在的记录

NOT EXISTS 就是反过来

SELECT ID,
       NAME
FROM A
WHERE not EXISTS
(
    SELECT * FROM B WHERE A.ID = B.AID
);

执行结果为

ID	Name
3	A3

NOT EXIST

有时候我们需要查看当前数据集合中，根据某些字段分组，最大/最少的结果。除了使用上述的ROW_NUMBER() 也可以使用子查询EXISTS。

例如我们使用上面的数据表

Name	Sex	Class	FScore	sub
EN	女	1	69	Chinese
EN	女	1	76	English
EN	女	1	95	Maths
EN	女	2	55	Chinese
EN	女	2	56	English
EN	女	2	57	Maths
Fox	男	1	80	Chinese
May	女	2	89	Chinese
May	女	2	92	Maths
May	女	2	96	English
Xing	男	1	60	Chinese

如果想要某个学生最大分数

SELECT *
FROM #XinTest t
WHERE 1 = 1
      AND NOT  EXISTS
(
    SELECT 1
    FROM #XinTest
    WHERE 1 = 1
          AND t.Name = Name
          AND t.Sex = Sex
          AND t.Class = Class
		  AND t.FScore<FScore
);

如果想要某个学生最小分数


SELECT *
FROM #XinTest t
WHERE 1 = 1
      AND NOT  EXISTS
(
    SELECT 1
    FROM #XinTest
    WHERE 1 = 1
          AND t.Name = Name
          AND t.Sex = Sex
          AND t.Class = Class
		  AND t.FScore>FScore
);

得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）

1 桔子水果 20 2011-7-2

1 香蕉水果 15 2011-7-3

2 青菜蔬菜 19 2011-7-2

有一种方法可以实现：使用max和min函数

SELECT *
FROM #XinTest t
WHERE 1 = 1 and
    FScore=  
(
    SELECT min(FScore)
    FROM #XinTest
    WHERE 1 = 1
          AND t.Name = Name
          AND t.Sex = Sex
          AND t.Class = Class
);

SELECT *
FROM #XinTest t
WHERE 1 = 1 and
    FScore=  
(
    SELECT max(FScore)
    FROM #XinTest
    WHERE 1 = 1
          AND t.Name = Name
          AND t.Sex = Sex
          AND t.Class = Class
);

打赏赞