次の方法で共有

Удаление дубликатов и обновление таблиц с использованием JOIN

  • [アーティクル]

Я давно хотел придумать "живой" пример для удаления дубликатов и обновления таблиц с использованием JOIN, и он пришел ко мне сам собой. Только что пришлось написать небольшой скрипт T-SQL, на примере которого и продемонстрирую несколько советов.

Задачка: есть некоторая табличка Table1, содержащая записи колонки ID1 int, ID2 int, Date datetime. В этой таблице существуют повторяющиеся пары ID1 и ID2, а дубликатов по этим парам быть не должно. Необходимо сделать несколько действий: 1) Посчитать количество дубликатов 2) Оставить только записи с самой поздней датой 3) Посчитать записи сгрупированные по ID2 и обновить поле в таблице Table2. Сама по себе логика проста - в одной таблице собираются голоса от разных пользователей, привязанные к некоторым сущностям в Table2, в самой Table2 важны два поля ID2 int и Total int, первое - идентификатор сущности, второе число строк сгруппированых по ID2 в Table1.

Для начала посчитаем сколько у нас дубликатов:

SELECT ID1, ID2, count(*) FROM Table1 GROUP BY ID1, ID2 HAVING count(*) > 1

Потом сделаем выборку во временную таблицу уникальных существующих ID1 и ID2, после чего привяжем к ним последнюю дату голосования и обновим основную таблицу:

DECLARE @UniqueTable TABLE(id1 int, id2 int, date datetime)

INSERT INTO @UniqueTable
    SELECT id1, id2, MAX(date) FROM table1 GROUP BY id1, id2

TRUNCATE TABLE table1

INSERT INTO table1 (id1, id2, date)
    SELECT id1, id2, date FROM @UniqueTable

Теперь, когда у нас есть нужный набор данных, обновим поля таблицы Table2, используя выражения UPDATE и JOIN:

DECLARE @TempTable TABLE(id2 int, total int)

INSERT INTO @TempTable (id2, total) SELECT id2, Count(*) AS total FROM table1 GROUP BY id2

UPDATE table2 SET table2.total = [@TempTable].total
    FROM table2
    INNER JOIN @TempTable ON [@TempTable].id2 = table2.id2

Вот так можно решить задачу без использования курсоров. К сожалению, мало кто использует эту возможность - очень часто вижу, что при необходимости обновления коллекции, которая связана по уникальному ключу, используют курсоры, хотя без них можно прекрасно обойтись.

Советы по оптимизации как всегда будут приняты с благодарностью!

Comments

  • Anonymous
    April 30, 2008
    Не знаю позволит ли SQL Server выборку и удаление из одной и той же таблицы в одном запросе, т.к. не работаю с SQL Server'ом, но на стандартном SQL (почти) того же эффекта можно достичь следующим способом: DELETE FROM table1 AS t1a  WHERE t1a.date <> (SELECT MAX(t1b.date)    FROM table1 AS AS t1b    WHERE t1b.id1 = t1a.id1      AND t1b.id2 = t1a.id2); UPDATE table2 AS t2  SET t2.total = (SELECT COUNT(*)    FROM table1 AS t1    WHERE t1.id2 = t2.id2); "Почти", потому что если в таблице table1 присутствует несколько записей с одинаковым набором значений (id1, id2, date), то приведенный мною запрос оставит их все. Но, насколько я понимаю, по набору этих полей создан первичный/уникальный ключ (в противном случае ваш запрос был бы непредсказуем, т.к. оставлял бы случайную из записей), следовательно, это не должно создать проблем.