/*
演示:T-SQL 删除重复的记录
功能:将表二中把数据插入到表一,但表二中存在重复数据
操作结果:1,insert 批处理把表二中重复的记录插入到表一中,并没有像想象的那像过滤重复的数据
2,使用加标识和子链接的方式删除重复rows
*/
--创建临时表
CREATE TABLE #tb1([NAME] VARCHAR(10))
CREATE TABLE #tb2([NAME] VARCHAR(10))
--构建表数据
DECLARE @index INT
SET @index=0
WHILE @index<10
BEGIN
INSERT INTO #tb1([name])
VALUES('name' + CAST(@index AS VARCHAR(10)))
SET @index=@index+1
END
DECLARE @index2 INT
SET @index2=0
WHILE @index2<2
begin
SET @index=10
WHILE @index<20
BEGIN
INSERT INTO #tb2([name])
VALUES('name' + CAST(@index AS VARCHAR(10)))
SET @index=@index+1
END
SET @index2 = @index2+1
END
--插入重复的记录到表一中
INSERT INTO [#tb1] ([NAME])
SELECT [name] FROM [#tb2] b
WHERE NOT EXISTS (SELECT 1 FROM [#tb1] a WHERE a.[NAME]=b.[NAME])
SELECT * FROM [#tb1]
SELECT * FROM [#tb2]
--修改表机构
ALTER TABLE #tb1 ADD
ID int NOT NULL IDENTITY(1, 1),
CONSTRAINT PK_id PRIMARY KEY(ID)
--删除重复记录
DELETE p1
FROM #tb1 p1
INNER JOIN
(
SELECT
MAX(ID) AS ID,
[name]
FROM
#tb1
GROUP BY
[name]
HAVING
COUNT([name]) > 1
) p2
ON(
p1.[name] = p2.[name]
AND
p1.ID <> p2.ID
)
--删除重复记录后的结果
SELECT * FROM [#tb1]
--删除临时表
DROP TABLE #tb1
DROP TABLE #tb2