Могу ли я удалять дубликаты баз данных на основе нескольких столбцов?

I задал этот вопрос некоторое время назад, чтобы удалить дубликаты записей на основе столбца. Ответ отлично работал:

delete from tbl
where id NOT in
(
select  min(id)
from tbl
group by sourceid
)

Теперь у меня есть ситуация simillar, но определение дублирующей записи основано на нескольких столбцах. Как я могу изменить этот выше SQL, чтобы идентифицировать повторяющиеся записи, где уникальная запись определяется как конкатенированная с Col1 + Col2 + Col3. Я бы просто сделал что-то вроде этого?

delete from tbl
where id NOT in
(
select  min(id)
from tbl
group by col1, col2, col3
)

Ответ 1

Это показывает строки, которые вы хотите сохранить:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
SELECT col1, col2, col3 FROM x WHERE rn = 1;

Здесь показаны строки, которые вы хотите удалить:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
SELECT col1, col2, col3 FROM x WHERE rn > 1;

И как только вы будете счастливы, что эти два набора верны, следующее их фактически удалит:

;WITH x AS 
(
  SELECT col1, col2, col3, rn = ROW_NUMBER() OVER 
      (PARTITION BY col1, col2, col3 ORDER BY id)
  FROM dbo.tbl
)
DELETE x WHERE rn > 1;

Обратите внимание, что во всех трех запросах первые 6 строк идентичны, и только последующий запрос после CTE изменился.

Ответ 2

Попробуйте это. Я создал таблицу tblA с тремя столбцами.

CREATE TABLE tblA
(
id int IDENTITY(1, 1),
colA int, 
colB int, 
colC int
)

И добавил несколько повторяющихся значений.

INSERT INTO tblA VALUES (1, 2, 3)
INSERT INTO tblA VALUES (1, 2, 3)
INSERT INTO tblA VALUES (4, 5, 6)
INSERT INTO tblA VALUES (7, 8, 9)
INSERT INTO tblA VALUES (7, 8, 9)

Если вы замените select с помощью delete в приведенном ниже инструкциях, вы будете работать с несколькими столбцами.

SELECT MIN(Id) as id
FROM
(
SELECT COUNT(*) as aantal, a.colA, a.colB, a.colC
FROM tblA       a
INNER JOIN tblA b   ON b.ColA = a.ColA
                    AND b.ColB = a.ColB
                    AND b.ColC = a.ColC
GROUP BY a.id, a.colA, a.colB, a.colC
HAVING COUNT(*) > 1
) c
INNER JOIN tblA d ON d.ColA = c.ColA
                    AND d.ColB = c.ColB
                    AND d.ColC = c.ColC
GROUP BY d.colA, d.colB, d.colC