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

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:书香水墨 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值