SQL Server 2008 R2:从包含在视图中的表中删除重复行 [英] SQL Server 2008 R2: Delete duplicate rows from tables containing in view

问题描述

--创建表dup1

CREATE TABLE dup1
(
    cola VARCHAR(10),
    colb VARCHAR(10)
);

--插入记录

INSERT INTO dup1 VALUES('1','2');
INSERT INTO dup1 VALUES('1','2');
INSERT INTO dup1 VALUES('1','3');
INSERT INTO dup1 VALUES('1','4');
INSERT INTO dup1 VALUES('1','5');

--创建表 dup2

CREATE TABLE dup2
(
    cola VARCHAR(10),
    colb VARCHAR(10)
);

--插入记录

INSERT INTO dup2 VALUES('1','2');
INSERT INTO dup2 VALUES('1','2');
INSERT INTO dup2 VALUES('2','3');
INSERT INTO dup2 VALUES('2','4');
INSERT INTO dup2 VALUES('1','5');

--创建视图

CREATE VIEW V_Dup as
SELECT * FROM dup1 UNION ALL 
SELECT * FROM dup2;

注意:记录 1,2 和 1,5 在两个表中是重复的但想要保留原始数据.

Note: The records 1,2 and 1,5 is duplicate in both tables BUT want to keep the original data.

我的问题是如何从两个表中删除重复记录.

My question is how do I delete the duplicate records from both tables.

推荐答案

create temp.表:

create temp. table :

DECLARE @tempDuplicateTable AS TABLE(
    cola VARCHAR(10),
    colb VARCHAR(10)
)

插入重复行:

INSERT INTO @tempDuplicateTable
    ( cola, colb )
    (
        SELECT a.cola, a.colb FROM dup1 a
        INNER JOIN dup2 b ON b.cola = a.cola AND b.colb = a.colb
    )

从表 dup1 和 dup2 中删除重复数据:

delete duplicate data from both table dup1 and dup2 :

DELETE a FROM dup1 a INNER JOIN @tempDuplicateTable b ON b.cola = a.cola AND b.colb = a.colb
DELETE a FROM dup2 a INNER JOIN @tempDuplicateTable b ON b.cola = a.cola AND b.colb = a.colb

如果你只是想要这个结果:

if you just want this result :

 cola   colb
   1    2
   1    3
   1    4
   1    5
   2    3
   2    4

试试这个查询:

SELECT DISTINCT * FROM V_Dup 

或者你可以像这样修改你的视图:

or you can modify your View like this :

CREATE VIEW V_Dup as
    SELECT DISTINCT a.* FROM (
    SELECT * FROM dup1 UNION ALL 
    SELECT * FROM dup2
) a

这篇关于SQL Server 2008 R2:从包含在视图中的表中删除重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！