--添加测试数据
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