Удаление записей с повторением

[nw]

Удаление записей с повторением

Каким образом, средствами SQL, можно удалить записи, которые повторяются один или более раз? Select distinct тут не подходит, ибо он удаляет копии, а моя задача - удалить все записи с повторениями, а не только их повторения.

Наглядный пример.
На входе (id name surname):
1 Вася Иванов
2 Вася Петров
3 Коля Сидоров
4 Коля Сидоров

на выходе должно быть:
1 Вася Иванов
2 Вася Петров

Еще раз замечу, что этого необходимо добиться исключительно средствами SQL, точнее TSQL.

 

[Falc]

nw
Какая СУБД?

-~{}~ 17.08.04 15:22:

nw
если есть подзапросы, то примерно так:

DELETE FROM table
WHERE field IN ( 
  SELECT field 
  FROM table
  GROUP BY field
  HAVING COUNT(*) > 1 )

 

[nw]

MS SQL Server и так же Access 2000.

 

[Falc]

nw
Должно сработать попробуй.

Да и очень желательно навесить индекс на повторяемое поле.

 

[nw]

Да, отлично, все сработало. Спасибо.
Соответственно для выборки полей, у которых нет повторений я применил модификацию запроса:
SELECT * FROM names WHERE name NOT IN (SELECT name FROM names GROUP BY name HAVING COUNT(*) > 1 )
Ну, гипотетическая таблица names и поле name.

 

[Falc]

nw
Так тебе удалить надо было из базы или только на выводе?

 

[nw]

Мне были интересны обе операции. Главное логика.

 

[Falc]

nw
Просто селект можно и через лефт джоин сделать

 

[nw]

Просто селект можно и через лефт джоин сделать

Можно пример для полноты картины?

 

[Falc]

Как-то так:

SELECT *
FROM names n1
LEFT JOIN names n2 ON n1.name = n2.name AND n1.id != n2.id
WHERE n2.name IS NULL

 

[nw]

А вот продолжение задачки. Допустим, нам из той же таблицы необходимо выбрать только те записи, которые дублируются по одному полю, НО, условия усложняются и в выборку должны попасть записи, которые дублируются по именам (полю name) и имеют разные фамилии (поле surname). При этом, если у ВСЕХ записей, дублирующихся по имени (полю name) одинаковые еще и фамилии (поле surname), то в выборку они вобще не должны попасть.
Наглядный пример:
Таблица на входе: names
id | name | surname
1 | Peter | Petrov
2 | James | Jameson
3 | James | Jameson
4 | James | Johnson
5 | Rasmus | Lerdorf
6 | Rasmus | Lerdorf

Так вот в результирующей выборке должны быть только следующие записи:
2 | James | Jameson
3 | James | Jameson
4 | James | Johnson

А еще лучше, те же записи, только без повторений.
У меня пока получилось решить этот вопрос следующим некрасивым запросом:
SELECT * FROM names WHERE surname IN (SELECT DISTINCT n2.surname FROM names n1 LEFT JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname) AND name IN (SELECT DISTINCT n2.name FROM names n1 LEFT JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname) ORDER BY name

Прошу заметить, что все это для Transact SQL

 

[Falc]

nw
А запрос с лефт джойном сработал?

 

[nw]

Ругнулся только на != (заменил на <>) и ничего не выдал с WHERE n2.name IS NULL, но без этого сработал.

 

[Falc]

nw

Страно, без "WHERE n2.name IS NULL" сработать не должен. И что без WHERE выдал правильный результат?

 

[nw]

Смотря что считать правильным результатом. Я его видоизменял чуть
SELECT * FROM names n1 LEFT JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname

 

[Falc]

nw
У тебя получился совсем другой запрос.

Мне кажется должен сработать такой запрос:
SELECT DISTINCT name, surname
FROM names n1
JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname

 

[nw]

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][Драйвер ODBC Microsoft Access]
Ошибка синтаксиса в предложении FROM.

 

[Falc]

nw
SELECT DISTINCT name, surname
FROM names n1
INNER JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname

 

[nw]

[Microsoft][Драйвер ODBC Microsoft Access] Ссылка на поле 'name' может относиться к полям нескольких таблиц, перечисленных в предложении FROM инструкции SQL.

 

[Falc]

Falc
SELECT DISTINCT n1.name, n1.surname
FROM names n1
INNER JOIN names n2 ON n1.name = n2.name AND n1.surname <> n2.surname