Как найти, что дубликат оценивает в SQL Server
Я использую SQL Server 2008. У меня есть таблица
Customers
customer_number int
field1 varchar
field2 varchar
field3 varchar
field4 varchar
... и намного больше столбцов, которые не имеют значения для моих запросов.
Столбец customer_number является pk. Я пытаюсь найти дублирующиеся значения и некоторые различия между ними.
Помогите мне найти все строки, которые имеют то же
1) field1, field2, field3, field4
2) только 3 столбца равны, и один из них не (кроме строк из списка 1)
3) равные только 2 столбца и два из них не (кроме строк из списка 1, и перечислите 2),
В конце у меня будет 3 таблицы с этим, заканчивается и дополнительный groupId, который будет тем же для группы подобных (Например, поскольку 3 столбца равняются, строки, которые имеют 3 тех же равные столбца, будут отдельной группой),
Спасибо.
2 ответа
Самым простым, вероятно, было бы написать хранимую процедуру, чтобы перебирать каждую группу клиентов с дубликатами и вставлять совпадающие для каждого номера группы соответственно.
Однако я подумал об этом, и вы, вероятно, сможете сделать это с помощью подзапроса. Надеюсь, я не сделал его более сложным, чем следовало бы, но это должно дать вам то, что вы ищете для первой таблицы дубликатов (все четыре поля). Обратите внимание, что это не тестировалось, поэтому может потребоваться небольшая настройка.
По сути, он получает каждую группу полей, где есть дубликаты, номер группы для каждого, затем получает всех клиентов с этими полями и присваивает один и тот же номер группе.
INSERT INTO FourFieldsDuplicates(group_no, customer_no)
SELECT Groups.group_no, custs.customer_no
FROM (SELECT ROW_NUMBER() OVER(ORDER BY c.field1) AS group_no,
c.field1, c.field2, c.field3, c.field4
FROM Customers c
GROUP BY c.field1, c.field2, c.field3, c.field4
HAVING COUNT(*) > 1) Groups
INNER JOIN Customers custs ON custs.field1 = Groups.field1
AND custs.field2 = Groups.field2
AND custs.field3 = Groups.field3
AND custs.field4 = Groups.field4
Другие немного сложнее, но вам нужно будет расширить возможности. Тогда группы из трех полей будут следующими:
INSERT INTO ThreeFieldsDuplicates(group_no, customer_no)
SELECT Groups.group_no, custs.customer_no
FROM (SELECT ROW_NUMBER() OVER(ORDER BY GroupsInner.field1) AS group_no,
GroupsInner.field1, GroupsInner.field2,
GroupsInner.field3, GroupsInner.field4
FROM (SELECT c.field1, c.field2, c.field3, NULL AS field4
FROM Customers c
WHERE NOT EXISTS(SELECT d.customer_no
FROM FourFieldsDuplicates d
WHERE d.customer_no = c.customer_no)
GROUP BY c.field1, c.field2, c.field3
UNION ALL
SELECT c.field1, c.field2, NULL AS field3, c.field4
FROM Customers c
WHERE NOT EXISTS(SELECT d.customer_no
FROM FourFieldsDuplicates d
WHERE d.customer_no = c.customer_no)
GROUP BY c.field1, c.field2, c.field4
UNION ALL
SELECT c.field1, NULL AS field2, c.field3, c.field4
FROM Customers c
WHERE NOT EXISTS(SELECT d.customer_no
FROM FourFieldsDuplicates d
WHERE d.customer_no = c.customer_no)
GROUP BY c.field1, c.field3, c.field4
UNION ALL
SELECT NULL AS field1, c.field2, c.field3, c.field4
FROM Customers c
WHERE NOT EXISTS(SELECT d.customer_no
FROM FourFieldsDuplicates d
WHERE d.customer_no = c.customer_no)
GROUP BY c.field2, c.field3, c.field4) GroupsInner
GROUP BY GroupsInner.field1, GroupsInner.field2,
GroupsInner.field3, GroupsInner.field4
HAVING COUNT(*) > 1) Groups
INNER JOIN Customers custs ON (Groups.field1 IS NULL OR custs.field1 = Groups.field1)
AND (Groups.field2 IS NULL OR custs.field2 = Groups.field2)
AND (Groups.field3 IS NULL OR custs.field3 = Groups.field3)
AND (Groups.field4 IS NULL OR custs.field4 = Groups.field4)
Надеюсь, это даст правильные результаты, и я оставлю последнюю в качестве упражнения. :-D
Я не уверен, требуется ли вам проверка равенства в разных полях (например, field1 = field2).
В противном случае этого может быть достаточно.
Редактировать
Не стесняйтесь корректировать тестовые данные, чтобы предоставить нам входные данные, которые дают неверный результат в соответствии с вашими требованиями.
Тестовые данные
DECLARE @Customers TABLE (
customer_number INTEGER IDENTITY(1, 1)
, field1 INTEGER
, field2 INTEGER
, field3 INTEGER
, field4 INTEGER)
INSERT INTO @Customers
SELECT 1, 1, 1, 1
UNION ALL SELECT 1, 1, 1, 1
UNION ALL SELECT 1, 1, 1, NULL
UNION ALL SELECT 1, 1, 1, 2
UNION ALL SELECT 1, 1, 1, 3
UNION ALL SELECT 2, 1, 1, 1
Все равно
SELECT ROW_NUMBER() OVER (ORDER BY c1.customer_number)
, c1.field1
, c1.field2
, c1.field3
, c1.field4
FROM @Customers c1
INNER JOIN @Customers c2 ON c2.customer_number > c1.customer_number
AND ISNULL(c2.field1, 0) = ISNULL(c1.field1, 0)
AND ISNULL(c2.field2, 0) = ISNULL(c1.field2, 0)
AND ISNULL(c2.field3, 0) = ISNULL(c1.field3, 0)
AND ISNULL(c2.field4, 0) = ISNULL(c1.field4, 0)
Одно поле отличается
SELECT ROW_NUMBER() OVER (ORDER BY field1, field2, field3, field4)
, field1
, field2
, field3
, field4
FROM (
SELECT DISTINCT c1.field1
, c1.field2
, c1.field3
, field4 = NULL
FROM @Customers c1
INNER JOIN @Customers c2 ON c2.customer_number > c1.customer_number
AND c2.field1 = c1.field1
AND c2.field2 = c1.field2
AND c2.field3 = c1.field3
AND ISNULL(c2.field4, 0) <> ISNULL(c1.field4, 0)
UNION ALL
SELECT DISTINCT c1.field1
, c1.field2
, NULL
, c1.field4
FROM @Customers c1
INNER JOIN @Customers c2 ON c2.customer_number > c1.customer_number
AND c2.field1 = c1.field1
AND c2.field2 = c1.field2
AND ISNULL(c2.field3, 0) <> ISNULL(c1.field3, 0)
AND c2.field4 = c1.field4
UNION ALL
SELECT DISTINCT c1.field1
, NULL
, c1.field3
, c1.field4
FROM @Customers c1
INNER JOIN @Customers c2 ON c2.customer_number > c1.customer_number
AND c2.field1 = c1.field1
AND ISNULL(c2.field2, 0) <> ISNULL(c1.field2, 0)
AND c2.field3 = c1.field3
AND c2.field4 = c1.field4
UNION ALL
SELECT DISTINCT NULL
, c1.field2
, c1.field3
, c1.field4
FROM @Customers c1
INNER JOIN @Customers c2 ON c2.customer_number > c1.customer_number
AND ISNULL(c2.field1, 0) <> ISNULL(c1.field1, 0)
AND c2.field2 = c1.field2
AND c2.field3 = c1.field3
AND c2.field4 = c1.field4
) c