Блог

У меня есть 2 таблицы…

• Клиент
• CustomerIdentification

Таблица клиентов содержит 2 поля

• CustomerID varchar(20)
• Переменная Customer_Id_Link(50)

Таблица CustomerIdentification содержит 3 поля

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

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

Теперь таблица customer содержит следующие данные: 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 полей для всех записей клиентов из приведенного выше соответствия — если true, то свяжите записи клиентов

Например:

Сопоставьте идентификатор 1 A для CU-1, CU-2, CU-3, CU-4

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

Сопоставьте идентификатор 2 B для CU-3, CU-4

• Несоответствие идентификаторов отсутствует
• Link CU-3 and CU-4 (update Customer_Id_Link field with a common value in customer table for both)

Match ID 3 A for CU-1, CU-4

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

Сопоставьте идентификатор 3 B для CU-2, CU-5

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

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

— Стандартный 64-разрядный SQL Server 2008 R2

Обновить——————————-

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

Следующий общий идентификатор и число — 2-b, который существует в CU-3 и CU-4. Эти два клиента на самом деле одинаковы, потому что оба имеют идентификатор 1 — A и ИДЕНТИФИКАТОР 2 — B. ИДЕНТИФИКАТОР 4 — C и ИДЕНТИФИКАТОР 3 — A не имеют значения, потому что оба идентификатора разные. Что по сути означает, что у этого клиента есть 4 идентификатора I A, 2 B, 4 C и 3 A. Итак, теперь нам нужно связать этого клиента с общим уникальным значением (guid) в таблице customer.

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

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

 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]