Очень сложная проблема с запросом SQL

У меня 2 стола...

  • Клиент
  • Идентификация клиента

Таблица клиентов имеет 2 поля

  • Идентификатор клиента varchar (20)
  • Customer_Id_Link varchar(50)

Таблица CustomerIdentification имеет 3 поля

  • Идентификатор клиента varchar (20)
  • Идентификационный_номер varchar(50)
  • Personal_ID_Type_Code int -- это внешний ключ к другой таблице, но это не имеет значения

По сути, Customer — это основная таблица клиентов (с CustomerID в качестве первичного ключа), а CustomerIdentification может иметь несколько элементов идентификации для данного клиента. Другими словами, CustomerId в CustomerIdentification является внешним ключом к таблице Customer. У клиента может быть много частей идентификации, каждая из которых имеет Identification_Number и Personal_ID_Type_Code (это целое число, которое говорит вам, является ли идентификация паспортом, грехом, водительскими правами и т. д.).

Теперь в таблице клиентов есть следующие данные: Customer_Id_Link на данный момент пусто (пустая строка)

CustomerId      Customer_Id_Link
--------------------------------
 'CU-1'         <Blank>
 'CU-2'         <Blank>
 'CU-3'         <Blank>
 'CU-4'         <Blank>
 'CU-5'         <Blank>

и таблица CustomerIdentification имеет следующие данные:

CustomerId    Identification_Number    Personal_ID_Type_Code
------------------------------------------------------------
'CU-1'        'A'                      1
'CU-1'        'A'                      2
'CU-1'        'A'                      3
'CU-2'        'A'                      1
'CU-2'        'B'                      3
'CU-2'        'C'                      4
'CU-3'        'A'                      1
'CU-3'        'B'                      2
'CU-3'        'C'                      4
'CU-4'        'A'                      1
'CU-4'        'B'                      2
'CU-4'        'B'                      3
'CU-5'        'B'                      3

По сути, несколько клиентов могут иметь одни и те же Identification_Number и Personal_ID_Type_Code в CustomerIdentification. Когда это происходит, все поля Customer_Id_Link должны быть обновлены общим значением (это может быть GUID или что-то еще). Но обработка для этого более сложная.

Правила таковы:

Для сопоставления полей Personal_ID_Type_Code и Identification_Number между записями о клиентах — сравните поля Identification_Number для всех других общих полей Personal_ID_Type_Code для всех записей о клиентах из приведенного выше совпадения — если да, то свяжите записи о клиентах.

Например:

ID совпадения 1 A для CU-1, CU-2, CU-3, CU-4

  • Несоответствие идентификатора исключения 2 (A в CU-1 и B в CU-3)
  • Связывание не выполнено

Идентификатор матча 2 B для CU-3, CU-4

  • Нет несоответствия идентификатора
  • Свяжите CU-3 и CU-4 (обновите поле Customer_Id_Link с общим значением в таблице клиентов для обоих)

ID совпадения 3 A для CU-1, CU-4

  • Несоответствие идентификатора исключения 2 (A и B)
  • Связывание не выполнено

Идентификатор матча 3 B для CU-2, CU-5

  • Нет несоответствия идентификатора
  • Свяжите CU-2 и CU-5 (обновите поле Customer_Id_Link общим значением в таблице клиентов для обоих) Идентификатор совпадения 4 C для CU-2, CU-3
  • CU-2 уже связан, сохраните CU-5 в списке связывания клиентов
  • CU-3 уже связан, сохраните CU-4 в списке связывания клиентов
  • Несоответствие идентификатора исключения 3 (B на CU-2 и A на CU-4)
  • Связывание не выполнено (предыдущее связывание остается)

Любая помощь будет оценена. Это не дает мне спать уже два дня, и я не могу найти решение. В идеале решением будет хранимая процедура, которую я могу выполнить для установления связи с клиентом.

- SQL Server 2008 R2 Стандартная 64-разрядная версия

ОБНОВЛЕНИЕ-----------------

Я знал, что будет сложно объяснить эту проблему, поэтому я беру на себя вину. Но, по сути, я хочу иметь возможность связать всех клиентов с одинаковыми идентификационными номерами, только у клиента может быть более 1 идентификационного номера. Возьмем пример 1. 1 A (1 – Personal_id_type_code, а A – идентификационный номер, существующий для 4 разных клиентов. CU-1, CU-2, CU-3, CU-4. Таким образом, потенциально это может быть один и тот же клиент, который существует 4 раза в таблица клиентов с разными идентификаторами клиентов.Нам нужно связать их с 1 общим значением.Однако у CU-1 есть 2 других идентификатора и если хотя бы 1 из них отличается от других 3 (CU-2, CU-3, CU-4 ) они не являются одним и тем же клиентом. Таким образом, идентификатор 2 с номером A не совпадает с идентификатором 2 для CU-3 (его B) и таким же для CU-4. Кроме того, даже несмотря на то, что идентификатор 2 номер A не существует в CU-2 , идентификатор 3 CU-1 и номер A не совпадают с идентификатором 3 CU-2 (его B), поэтому это вообще не совпадение.

Следующими общими идентификаторами и номерами являются 2-b, которые существуют в CU-3 и CU-4. Эти два клиента на самом деле являются одним и тем же, потому что оба имеют идентификатор 1 - A и идентификатор 2 - B. ID 4 - C и идентификатор 3 - A не имеют значения, потому что оба идентификатора разные. По сути, это означает, что у этого клиента есть 4 идентификатора I A, 2 B, 4 C и 3 A. Итак, теперь нам нужно связать этого клиента с общим уникальным значением (guid) в таблице клиентов.

Надеюсь, теперь я объяснил этот очень сложный вопрос. Это трудно объяснить, поскольку это очень уникальная проблема.


sql-server sql-server-2008 sql-server-2008-r2
person Vince    schedule 04.11.2011    source источник
comment
Ваши критерии все еще не очень ясны... можете ли вы уточнить?   -  person JNK    schedule 05.11.2011
comment
Вы пытаетесь определить, какие идентификаторы клиентов относятся к одному клиенту? И для каждого Personal_ID_Type_Code два общих идентификатора клиента, если их Identification_Number также совпадают, эти два идентификатора клиента идентифицируют одного клиента. И вы идентифицируете эти совпадающие идентификаторы клиентов по значению в Customer.Customer_Id_Link. Это правильно?   -  person Mike Sherrill 'Cat Recall'    schedule 05.11.2011
comment
Верно. За исключением того, что если идентификаторы клиентов имеют 1 общий Personal_ID_Type_Code и идентификационный номер, то другие идентификаторы также должны совпадать или иметь разные Personal_ID_Type_Code, чтобы они были одним и тем же клиентом.   -  person Vince    schedule 05.11.2011

Ответы (1)


arrow_upward
2
arrow_downward

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

CREATE TABLE [dbo].[Customer]
(
    [CustomerName]      VARCHAR(20)     NOT NULL,
    [CustomerLink]      VARBINARY(20)   NULL
)

CREATE TABLE [dbo].[CustomerIdentification]
(
    [CustomerName]      VARCHAR(20)     NOT NULL,
    [ID]                VARCHAR(50)     NOT NULL,
    [IDType]            VARCHAR(16)     NOT NULL
)

И я добавил еще несколько тестовых данных.

INSERT  [dbo].[Customer]
        ([CustomerName])
VALUES  ('Fred'),
        ('Bob'),
        ('Vince'),
        ('Tom'),
        ('Alice'),
        ('Matt'),
        ('Dan')

INSERT  [dbo].[CustomerIdentification]
VALUES  
        ('Fred',    'A',    'Passport'),
        ('Fred',    'A',    'SIN'),
        ('Fred',    'A',    'Drivers Licence'),
        ('Bob',     'A',    'Passport'),
        ('Bob',     'B',    'Drivers Licence'),
        ('Bob',     'C',    'Credit Card'),
        ('Vince',   'A',    'Passport'),
        ('Vince',   'B',    'SIN'),
        ('Vince',   'C',    'Credit Card'),
        ('Tom',     'A',    'Passport'),
        ('Tom',     'B',    'SIN'),
        ('Tom',     'B',    'Drivers Licence'),
        ('Alice',   'B',    'Drivers Licence'),
        ('Matt',    'X',    'Drivers Licence'),
        ('Dan',     'X',    'Drivers Licence')

Это то, что вы ищете:

;WITH [cteNonMatchingIDs] AS (
    -- Pairs where the IDType is the same, but 
    -- name and ID don't match
    SELECT  ci3.[CustomerName] AS [CustomerName1],
            ci4.[CustomerName] AS [CustomerName2]
    FROM [dbo].[CustomerIdentification] ci3
    INNER JOIN [dbo].[CustomerIdentification] ci4
        ON ci3.[IDType] = ci4.[IDType]
    WHERE ci3.[CustomerName] <> ci4.[CustomerName]
    AND ci3.[ID] <> ci4.[ID]
),
[cteMatchedPairs] AS (
    -- Pairs where the IDType and ID match, and
    -- there aren't any non matching IDs for the
    -- CustomerName
    SELECT DISTINCT 
            ci1.[CustomerName] AS [CustomerName1],
            ci2.[CustomerName] AS [CustomerName2]
    FROM [dbo].[CustomerIdentification] ci1
    LEFT JOIN [dbo].[CustomerIdentification] ci2
        ON ci1.[CustomerName] <> ci2.[CustomerName]
        AND ci1.[IDType] = ci2.[IDType] 
    WHERE ci1.[ID] = ISNULL(ci2.[ID], ci1.[ID])
    AND NOT EXISTS (
        SELECT 1
        FROM [cteNonMatchingIDs]
        WHERE ci1.[CustomerName] = [CustomerName1] -- correlated subquery
        AND ci2.[CustomerName] = [CustomerName2]
    )
    AND ci1.[CustomerName] < ci2.[CustomerName]
),
[cteMatchedList] ([CustomerName], [CustomerNameList]) AS (
    -- Turn the matched pairs into list of matching
    -- CustomerNames
    SELECT  [CustomerName1],
            [CustomerNameList]
    FROM (
        SELECT  [CustomerName1],
                CONVERT(VARCHAR(1000), '$'
                 + [CustomerName1] + '$'
                 + [CustomerName2]) AS [CustomerNameList]
        FROM [cteMatchedPairs]
        UNION ALL
        SELECT  [CustomerName2],
                CONVERT(VARCHAR(1000), '$'
                 + [CustomerName2]) AS [CustomerNameList]
        FROM [cteMatchedPairs]
    ) [cteMatchedPairs]
    UNION ALL
    SELECT  [cteMatchedList].[CustomerName],
            CONVERT(VARCHAR(1000),[CustomerNameList] + '$'
             + [cteMatchedPairs].[CustomerName2])
    FROM [cteMatchedList] -- recursive CTE
    INNER JOIN [cteMatchedPairs]
        ON RIGHT([cteMatchedList].[CustomerNameList],
         LEN([cteMatchedPairs].[CustomerName1])
        ) = [cteMatchedPairs].[CustomerName1]
),
[cteSubstringLists] AS (
    SELECT  r1.[CustomerName],
            r2.[CustomerNameList]
    FROM [cteMatchedList] r1
    INNER JOIN [cteMatchedList] r2
        ON r2.[CustomerNameList] LIKE '%' + r1.[CustomerNameList] + '%'
),
[cteCustomerLink] AS (
    SELECT DISTINCT 
            x1.[CustomerName],
            HASHBYTES('SHA1', x2.[CustomerNameList]) AS [CustomerLink]
    FROM (
        SELECT  [CustomerName],
                MAX(LEN([CustomerNameList])) AS [MAX LEN CustomerList]
        FROM [cteSubstringLists]
        GROUP BY [CustomerName]
    ) x1
    INNER JOIN (
        SELECT  [CustomerName],
                LEN([CustomerNameList]) AS [LEN CustomerList], 
                [CustomerNameList]
        FROM [cteSubstringLists]
    ) x2
        ON x1.[MAX LEN CustomerList] = x2.[LEN CustomerList]
        AND x1.[CustomerName] = x2.[CustomerName]
)
UPDATE  c
SET     [CustomerLink] = cl.[CustomerLink]
FROM [dbo].[Customer] c
INNER JOIN [cteCustomerLink] cl
    ON cl.[CustomerName] = c.[CustomerName]


SELECT *
FROM [dbo].[Customer]
person Tom Hunter    schedule 05.11.2011
comment
Это шаг в правильном направлении, Том, все еще не полностью исправлено, но мы движемся. У меня есть вопрос, но сначала избавьтесь от Мэтта и Дэна за обоими столами (так что у нас останется только 5 клиентов), так как они не нужны. Кроме того, обновите запись («Том», «Б», «Водительские права») на («Том», «А», «Водительские права»). Теперь, почему запрос возвращает разные результаты, если вы переименуете клиентов в то, что я изначально предоставил. Например: поместите CU-1 для Фреда, CU-2 для Боба, CU-3 для Винса, CU-4 для Тома и CU-5 для Алисы в обеих таблицах, и вы увидите, что результаты будут отличаться, что странно. - person Vince; 06.11.2011
comment
Отличие от того, используете ли вы настоящие человеческие имена, которые вы предоставили. Я думаю, что результаты должны быть одинаковыми в обоих случаях. нет?? - person Vince; 06.11.2011
comment
Кроме того, есть ли способ вместо использования HASHBYTES мы могли бы использовать NewID?? - person Vince; 07.11.2011
comment
Привет, Том, не могли бы вы помочь с моим первым вопросом. В зависимости от используемых имен, он производит разные выходные данные. Почему?? - person Vince; 08.11.2011
comment
Я думаю, что AND ci1.[CustomerName] ‹ ci2.[CustomerName] вызывает это странное поведение. - person Vince; 08.11.2011