--好久没有写博客了，发现记性越来越不好了，博客写起来，废话少说，直接上代码
--创建表
CREATE TABLE [dbo].[students](
[stu_id] [INT] IDENTITY(1,1) NOT NULL,
[stu_name] [NVARCHAR](20) NULL,
[stu_gender] [CHAR](1) NULL,
[stu_phone] [VARCHAR](15) NULL,
[stu_birthday] [DATE] NULL
) ON [PRIMARY]
GO
--添加测试数据
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 FROM dbo.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 BY stu_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 BY stu_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 BY stu_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 AS aa
LEFT JOIN (
SELECT MIN(stu_id) AS id FROM dbo.students
GROUP BY stu_name,stu_gender,stu_phone,stu_birthday
) AS bb
ON 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 FROM dbo.students;
DROP TABLE dbo.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(select stu_id
FROM (SELECT stu_id,ROW_NUMBER() OVER(PARTITION BY stu_name,stu_gender,stu_phone,stu_birthday ORDER BY stu_name) AS rn
FROM dbo.students ) s
WHERE 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 * FROM dbo.students2;
DROP TABLE dbo.students2
