Удаление дубликатов из виртуальной таблицы

Недопустимо удалять записи из запроса, т.е. мы не можем воспользоваться таким вариантом:

DELETE FROM (SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk

FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY name) num

FROM T

) X

т.к. в операторе DELETE допускается использовать только базовую таблицу или представление. Поэтому мы могли бы создать представление и удалить записи уже из него. Конечно, на самом деле записи удаляются из базовой таблицы, на которой создано представление. Итак, мы можем поступить следующим образом:

CREATE VIEW Tview

AS

SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk

FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num

FROM T

) X

GO

DELETE FROM Tview

"Опять что-то создавать", - скажете вы. Не обязательно, и, чтобы доказать это, нам помогут общие табличные выражения (CTE), которые можно назвать виртуальными представлениями. CTE, хотя и не являются сохраняемыми в базе данных объектами, могут использоваться с операторами обновления. В результате все сводится к одному запросу:

WITH CTE AS

(SELECT name, RANK() OVER (PARTITION BY name ORDER BY num) rnk

FROM(SELECT *, ROW_NUMBER() OVER(ORDER BY name) num

FROM T

) X

)

DELETE FROM CTE

WHERE rnk > 1;

Не забудьте только создать первичный ключ. :-)

06-10-2009

Попал по внешней ссылке на эту статью и решил себе возразить. :-)

Вот эта фраза: "Мы не можем сразу ранжировать строки просто потому, что их не по чем ранжировать."

Разумеется, это правильно, но мы можем отказаться от ранжирования (в ущерб обучению :-)), выполнив "псевдоранжирование". Дело в том, что есть возможность выполнить независимую нумерацию для каждой группы, если в предложении OVER для функции ROW_NUMBER использовать конструкцию PARTITION BY. Итак, можно вообще обойтись без функции RANK, если выполнить разбиение по name

SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name)

Это упростит все последующие запросы, в частности, последнее решение задачи удаления дубликатов можно переписать в виде:

WITH CTE AS (

SELECT name, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) rnk

FROM T

)

DELETE FROM CTE

Dzone.com