删除重复的SQL表行

Question

我的sql表是没有主键列的。我可以找到一字排开的行，但我不知道怎样才能把它挪到一排去休息。

col1     col2    col3    col4
10       0       1000    1    
10       0       1000    1    --> should be deleted
10       0       1111    2    --> should be deleted
10       1       1000    1
10       2       1000    1
15       0       1000    1
15       0       1000    1    --> should be deleted
16       0       1000    1

我使用col1和col2来理解行是一式两份的。同时，"10“"0”必须是唯一的，但是表可以包含许多"10“或许多"0”值。

谢谢。

Answer 1

这应该能行。它首先找出每个组合有多少个副本，然后删除除一个以外的所有副本。

CREATE TABLE t_test (col1 int, col2 int, col3 int, col4 int)

INSERT t_test 
          SELECT 10, 0, 1000, 1 
UNION ALL SELECT 10, 0, 1000, 1 --> should be deleted
UNION ALL SELECT 10, 0, 1111, 2 --> should be deleted
UNION ALL SELECT 10, 1, 1000, 1
UNION ALL SELECT 10, 2, 1000, 1
UNION ALL SELECT 15, 0, 1000, 1
UNION ALL SELECT 15, 0, 1000, 1 --> should be deleted
UNION ALL SELECT 16, 0, 1000, 1

DECLARE @col1 int, @col2 int, @count int

DECLARE delete_loop CURSOR LOCAL STATIC
    FOR SELECT COUNT(*), col1, col2
          FROM t_test
         GROUP BY col1, col2
        HAVING COUNT(*) > 1
OPEN delete_loop
FETCH NEXT FROM delete_loop INTO @count, @col1, @col2
WHILE @@FETCH_STATUS = 0
    BEGIN
        DELETE TOP (@count - 1)
          FROM t_test
         WHERE col1 = @col1
           AND col2 = @col2

        FETCH NEXT FROM delete_loop INTO @count, @col1, @col2
    END
CLOSE delete_loop
DEALLOCATE delete_loop

SELECT * FROM t_test

编辑:适应于只查看col1和col2的唯一性。

Answer 2

这里有一个简单的方法来识别和删除重复的内容。

为col1 & col2 (分区)的每个组合添加一个id，将其封装在一个CTE中，并删除不等于1的记录(第一次出现)。

DECLARE @Test TABLE (col1 int, col2 int, col3 int, col4 int)

INSERT @Test 
          SELECT 10, 0, 1000, 1 
UNION ALL SELECT 10, 0, 1000, 1 --> should be deleted
UNION ALL SELECT 10, 0, 1111, 2 --> should be deleted
UNION ALL SELECT 10, 1, 1000, 1
UNION ALL SELECT 10, 2, 1000, 1
UNION ALL SELECT 15, 0, 1000, 1
UNION ALL SELECT 15, 0, 1000, 1 --> should be deleted
UNION ALL SELECT 16, 0, 1000, 1

;WITH DUPES
AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY COL1,COL2 ORDER BY COL1,COL4) AS myID
FROM @Test
)

DELETE D
FROM DUPES D
WHERE myID <> 1

SELECT * 
FROM @Test