Удалить дубликаты из таблицы

Question

Есть таблица

Как мне удалить дубликаты запросом sql?

Answer 1

PostgreSQL

Можно решить задачу одним запросом с CTE (где T - исходная таблица):

    with td as
    (delete from T returning *),
    tt as
    (select row_number() over(partition by id_category,id_product,position 
                  order by id_category,id_product,position) num, * from td)
    insert into T select id_category,id_product,position from tt where num=1;

Answer 2

MySQL:

Можно создать столбец с уникальными значениями. С помощью него удалить дубликаты, потом удалить столбец(а лучше оставить и навесить на него уникальный индекс). Что довольно ресурсоёмко, но вариант.

Создать столбец и заполнить его натуральными числами можно так:

ALTER TABLE Test ADD Id INT;

UPDATE Test
  SET Id = @I := @I + 1
/*тут можно задать нужную сортировку при желании, я добавил по A, B*/
ORDER BY A, B, (SELECT @I := 0)

В итоге в таблице Test появится колонка id, заполненная числовой последовательностью отсортированной по столбцам A, B.

UPD: Есть несколько экстравагантный способ:) Сначала пометить строки на удаление, потом удалить. Используя опять же накопление в переменную.

Для наглядности покажу все скрипты в рабочем виде.

Создаём табличку и заполняем:

CREATE TABLE TEST_DUPLICATE(
  A VARCHAR(20),
  B VARCHAR(20)
);
INSERT TEST_DUPLICATE SELECT 'AAA', 'BBB';
INSERT TEST_DUPLICATE SELECT 'AAA', 'BBB';
INSERT TEST_DUPLICATE SELECT 'BBB', 'BBB';
INSERT TEST_DUPLICATE SELECT 'AAA', 'AAA';
INSERT TEST_DUPLICATE SELECT 'BBB', 'BBB';
INSERT TEST_DUPLICATE SELECT 'AAA', 'AAA';
INSERT TEST_DUPLICATE SELECT 'AAA', 'BBB';

SELECT *
FROM TEST_DUPLICATE;

Вот её содержимое:

AAA       BBB
AAA       BBB
BBB       BBB
AAA       AAA
BBB       BBB
AAA       AAA
AAA       BBB

Теперь пометим в поле B дубликаты строкой DUPLICATED

UPDATE TEST_DUPLICATE
SET B = CONCAT(
  CASE WHEN A=@A AND B=@B THEN 'DUPLICATED' ELSE B END
  , /*тут фейковое слагаемое, просто чтобы изменить значения @A и @B*/
  CASE WHEN CONCAT((@A:=A),(@B:=B)) >= '' THEN '' END)
ORDER BY A, B, (SELECT @A:=''), (SELECT @B:='')  ;

SELECT *
FROM TEST_DUPLICATE;

Теперь содержимое таблицы:

AAA       BBB
AAA       DUPLICATED
BBB       BBB
AAA       AAA
BBB       DUPLICATED
AAA       DUPLICATED
AAA       DUPLICATED

Удаляем помеченные строки:

DELETE FROM TEST_DUPLICATE 
WHERE B = 'DUPLICATED';

SELECT *
FROM TEST_DUPLICATE;

получили что хотели:

AAA       BBB
BBB       BBB
AAA       AAA

Есть определённая критика такого решения. Но я для простоты самого подхода описал. При желании можно тему развить и использовать.

Дополнение: Всё то же самое можно сделать и на других СУБД, заменив накопление в переменную аналитическими функциями ROW_NUMBER, LEAD. В других СУБД это и выглядеть будет "симпатичней".

Answer 3

1) Через другую таблицу

CREATE TEMPORARY TABLE tmp_tab AS SELECT DISTINCT * FROM your_table;
DELETE FROM your_table;
INSERT INTO your_table SELECT * FROM tmp_tab;
DROP TABLE tmp_tab;

2) Добавлением индекса. Лично сам так не пробовал, но говорят работает. Добавляется уникальный индекс, а дубли удаляются. Актуально для MySQL

ALTER IGNORE TABLE your_table ADD UNIQUE INDEX(id_category, id_product, position);

Answer 4

Вот простое решение через JOIN, у меня на MySQL сработало.

DELETE tbl.* FROM my_table AS tbl
JOIN my_table AS tbl1 ON tbl.name=tbl1.name AND tbl.id>tbl1.id

Источник