Как заставить код T-SQL находить дубликаты?

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

Однако вот как создать SQL для получения дубликатов по набору столбцов:

SELECT col1, col2, col3, col4
FROM table
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1

Это найдет строки, которые для столбцов col1-col4 имеют одну и ту же комбинацию значений более одного раза.

Например, в следующей таблице строки 2+3 будут повторяться:

PK    col1    col2    col3    col4    col5
1       1       2       3       4      6
2       1       3       4       7      7
3       1       3       4       7      10
4       2       3       1       4      5

Две строки имеют общие значения в столбцах col1-col4 и, таким образом, в соответствии с этим SQL считаются дубликатами. Разверните список столбцов, чтобы он содержал все столбцы, для которых вы хотите это проанализировать.

Если вы используете SQL Server 2005+, вы можете использовать следующий код, чтобы увидеть все строки вместе с другими столбцами:

SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber
FROM table

Вы также можете удалить (или иным образом работать с) дубликаты, используя эту технику:

WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber
    FROM table
)
DELETE FROM cte WHERE DuplicateRowNumber > 1

ROW_NUMBER чрезвычайно мощен — с ним можно многое сделать — см. статью о BOL по адресу http://msdn.microsoft.com/en-us/library/ms186734.aspx

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

SELECT * FROM db WHERE col IN
    (SELECT col FROM db GROUP BY col HAVING COUNT(*) > 1)
    ORDER BY col

АФАИК, это не так. Просто сделайте оператор select, сгруппировав его по всем полям таблицы и отфильтровав с помощью предложения hasing, где количество больше 1.

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

Другой способ сделать это — соединить таблицу с самой собой.

SELECT *
FROM dbo.TableA aBase
JOIN dbo.TableA aDupes ON aDupes.ColA = aBase.ColA AND
                          aDupes.ColB = aBase.ColB
WHERE aBase.Pkey < aDupes.Pkey

Примечание. Ключ aBase.Pkey ‹ aDupes.Pkey существует потому, что присоединение таблицы против себя создаст две строки на совпадение, поскольку условие всегда будет выполняться дважды.

Другими словами: если в таблице aBase есть строка, равная строке из aDupes (на основе ColA и ColB), отражение этого совпадения также будет верным — что aDupes имеет строку, равную строке aBase на основе ColA и ColB. Поэтому оба этих совпадения будут возвращены в наборе результатов.

Сузьте это/устраните это отражение, произвольно выбрав все результаты, где одна из таблиц имеет более низкий ключ.

‹ или > не имеет значения, если используются разные ключи.

Это также обеспечивает фильтрацию совпадений со строкой самой по себе, поскольку aBase.Pkey ‹ aDupes.Pkey заставляет первичные ключи отличаться.