Как удалить дубликаты строк в sybase, если у вас нет уникального ключа?

Да, вы можете найти подобные вопросы много раз, но: самые элегантные решения, размещенные здесь, работают для SQL Server, но не для Sybase (в моем случае Sybase Anywhere 11). Я даже нашел некоторые вопросы, связанные с Sybase, помеченные как дубликаты для вопросов SQL Server, что не помогает.

Одним из примеров решений, которые мне понравились, но не сработали, является конструкция WITH ... DELETE ....

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

Я надеюсь на хороший, простой и быстрый запрос, просто удалив все, кроме одного точного дубликата.

Вот небольшой фреймворк для тестирования: 

IF OBJECT_ID( 'tempdb..#TestTable' ) IS NOT NULL
  DROP TABLE #TestTable;

CREATE TABLE #TestTable (Column1 varchar(1), Column2 int);

INSERT INTO #TestTable VALUES ('A', 1);
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 1); -- duplicate
INSERT INTO #TestTable VALUES ('A', 2);
INSERT INTO #TestTable VALUES ('B', 1);
INSERT INTO #TestTable VALUES ('B', 2);
INSERT INTO #TestTable VALUES ('B', 2); -- duplicate
INSERT INTO #TestTable VALUES ('C', 1);
INSERT INTO #TestTable VALUES ('C', 2);

SELECT * FROM #TestTable ORDER BY Column1,Column2;

DELETE <your solution here>

SELECT * FROM #TestTable ORDER BY Column1,Column2;
5 2
sql duplicate-removal sybase

5 ответов:

Если все поля идентичны, вы можете просто сделать это: 

select distinct * 
into #temp_table
from table_with_duplicates 

delete table_with_duplicates 

insert into table_with_duplicates select * from #temp_table

Если все поля не идентичны, например, если у вас есть id, который отличается, то вам нужно будет перечислить все поля в инструкции select и жестко закодировать значение в id, чтобы сделать его идентичным, если это поле вас не волнует. Например: 

insert #temp_table field1, field2, id select (field1, field2, 999)
from table_with_duplicates
5

Это работает хорошо и быстро: 

DELETE FROM #TestTable
WHERE ROWID(#TestTable) IN (
  SELECT rowid FROM (
    SELECT ROWID(#TestTable) rowid, 
      ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1,Column2) rownum
    FROM #TestTable
  ) sub
  WHERE rownum > 1
);
Если вы не знаете OVER(PARTITION BY ...), просто выполните внутренний оператор SELECT, чтобы увидеть, что он делает.
1

Вот еще один интересный пример, который я нашел и принял: 

DELETE FROM #TestTable dupes
FROM #TestTable dupes, #TestTable fullTable
WHERE dupes.Column1 = fullTable.Column1
  AND dupes.Column2 = fullTable.Column2
  AND ROWID(dupes) > ROWID(fullTable);

Или, если вам больше нравятся явные соединения (я это делаю): 

DELETE FROM #TestTable dupes
FROM #TestTable dupes
INNER JOIN #TestTable fullTable
  ON dupes.Column1 = fullTable.Column1
  AND dupes.Column2 = fullTable.Column2
  AND ROWID(dupes) > ROWID(fullTable);

Или короткая форма ("естественное" соединение автоматически включает идентичные имена столбцов): 

DELETE FROM #TestTable dupes
FROM #TestTable dupes
NATURAL JOIN #TestTable fullTable
  ON ROWID(dupes) > ROWID(fullTable);

...если кто-то находит решение, не требующее ROWID(), мне было бы интересно посмотреть на них.

0

Пожалуйста, попробуйте это:

create clustered index i1 on table table_name(column_name) with ignore_dup_row


create table #test(id int,name char(9))
insert into #test values(1,"A")
insert into #test values(1,"A")
create clustered index i1 on #test(id) with ignore_dup_row
select * from #test
0

Хорошо, теперь, когда я знаю функцию ROWID(), решения для таблиц с первичным ключом (PK) могут быть легко приняты. Сначала он выбирает все строки для сохранения, а затем удаляет оставшиеся: 

DELETE FROM #TestTable
FROM #TestTable
LEFT OUTER JOIN (
  SELECT MIN(ROWID(#TestTable)) rowid
  FROM #TestTable
  GROUP BY Column1, Column2
) AS KeepRows ON ROWID(#TestTable) = KeepRows.rowid
WHERE KeepRows.rowid IS NULL;

...или как насчет этого более короткого варианта? Мне нравится!

DELETE FROM #TestTable
WHERE ROWID(#TestTable) NOT IN (
  SELECT MIN(ROWID(#TestTable))
  FROM #TestTable
  GROUP BY Column1, Column2
);

В этом посте, который вдохновил меня больше всего, есть комментарий, который NOT IN может быть медленнее. Но это для SQL server, и иногда элегантность важнее :) - я тоже думаю, что все зависит от хорошего индексы.

В любом случае, обычно это плохой дизайн, чтобы иметь таблицы без PK. Вы должны, по крайней мере, добавить идентификатор "autoinc", и если вы это сделаете, вы можете использовать этот идентификатор вместо функции ROWID(), которая является нестандартным расширением Sybase (некоторые другие тоже имеют его).

-1