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

#php #mysql #sql #postgresql #phpmyadmin

Есть таблица 



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

Ответы

Ответ 1

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);


Ответ 2

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;


Ответ 3

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. В других СУБД это и выглядеть
будет "симпатичней".