SqlServer去重

--添加测试数据

INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('宋江', '1', '13142224589', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('卢俊义', '1', '18942224222', '2000-04-11');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('吴用', '0', '13942224511', '2002-05-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('公孙胜', '1', '13442228589', '1902-05-09');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('关胜', '1', '17642224566', '2002-09-01');INSERT INTO [dbo].[students]([stu_name], [stu_gender], [stu_phone], [stu_birthday])VALUES('林冲', '1', '18642224989', '2011-08-07');GO

--查询重复数据

SELECT S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday HAVING COUNT(1)>1

--根据某个字段去重

SELECT stu_name,s.stu_phone FROM ( SELECT stu_name,stu_phone ,ROW_NUMBER() OVER (PARTITION BY stu_name ORDER BY stu_phone) rn FROM dbo.students )AS s WHERE rn=1

--查询去重，写法1

SELECT DISTINCT stu_name,stu_gender,stu_phone,stu_birthday FROMdbo.students--查询去重，写法2

SELECT stu_id, stu_name,stu_gender,stu_phone,stu_birthday FROM dbo.students WHERE stu_id IN(SELECT MAX(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法1

DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday)--删除重复的数据,方法2

DELETE FROM dbo.students WHERE NOT EXISTS(SELECT 1 FROM (SELECT MIN(S.stu_id) AS id FROM dbo.students S GROUP BY S.stu_name,S.stu_gender,S.stu_phone,S.stu_birthday) AS G WHERE students.stu_id=g.id)--删除重复的数据，方法3

CREATE TABLE #TBL(ID INT PRIMARY KEY);INSERT INTO #TBL(ID) SELECT MIN(stu_id) FROM dbo.students GROUP BYstu_name,stu_gender,stu_phone,stu_birthday;DELETE FROM dbo.students WHERE stu_id NOT IN(SELECT T.ID FROM#TBL T)DROP TABLE#TBL--删除重复的数据，方法4

DELETE dbo.students FROM dbo.students ASaaLEFT JOIN(SELECT MIN(stu_id) AS id FROMdbo.studentsGROUP BYstu_name,stu_gender,stu_phone,stu_birthday

)ASbbON aa.stu_id = bb.id WHERE bb.id is null

--无唯一列(或主键)去重--ALTER TABLE dbo.students DROP CONSTRAINT PK_students

ALTER TABLE dbo.students drop column stu_id;--创建数据，删除stu_id--无唯一列(或主键)去重，只保留一行，方法1：查询去重后的数据后放到另一个表，删除原表，然后重命名--注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。

SELECT DISTINCT * INTO dbo.students2 FROMdbo.students;DROP TABLEdbo.students;EXEC sp_rename 'students2', 'students';--无唯一列(或主键)去重，只保留一行，方法2：增加唯一列

ALTER TABLE dbo.students ADD stu_id INT IDENTITY(1,1);DELETE FROM dbo.students WHERE stu_id NOT IN(selectstu_idFROM (SELECT stu_id,ROW_NUMBER() OVER(PARTITION BY stu_name,stu_gender,stu_phone,stu_birthday ORDER BY stu_name) ASrnFROMdbo.students ) sWHERE rn=1);--无唯一列(或主键)去重，只保留一行，方法3：将重复以及不重复数据去重插入新表，删除旧表相应数据，再把新表数据插入旧表

SELECT DISTINCT * INTO students2 FROM dbo.students WHERE stu_birthday>='2002-09-01';--条件

DELETE FROM dbo.students WHERE stu_birthday>='2002-09-01';INSERT INTO dbo.students(stu_name, stu_gender, stu_phone, stu_birthday) SELECT * FROMdbo.students2;DROP TABLE dbo.students2