sql-server - 一个非常复杂的SQL查询问题

Question

我有2张桌子...


顾客
客户识别


客户表有2个字段


CustomerId varchar（20）
Customer_Id_Link varchar（50）


CustomerIdentification表具有3个字段


CustomerId varchar（20）
Identification_Number varchar（50）
Personal_ID_Type_Code int-是另一个表的外键，但是那无关紧要


基本上，客户是客户主表（以客户ID作为主键），客户标识可以具有给定客户的多个标识。换句话说，CustomerIdentification中的CustomerId是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字段
-如果为true，则链接客户记录

例如：

CU-1，CU-2，CU-3，CU-4的匹配ID 1 A


异常ID 2不匹配（CU-1上的A与CU-3上的B）
没有完成关联


匹配ID 2 B，用于CU-3，CU-4


没有ID不符
链接CU-3和CU-4（用两个客户表中的公共值更新 Customer_Id_Link字段）


CU-1，CU-4的匹配ID 3 A


异常ID 2不匹配（A与B）
没有完成关联


CU-2，CU-5的匹配ID 3 B


没有ID不符
链接CU-2和CU-5（使用两个客户表中的公共值更新 Customer_Id_Link字段）CU-2，CU-3的匹配ID 4 C
CU-2已链接，请将CU-5保留到客户链接列表
CU-3已链接，请将CU-4保留到客户链接列表
异常ID 3不匹配（CU-2上的B与CU-4上的A）
未完成关联（仍保持先前的关联）


任何帮助将不胜感激。这让我醒了两天了，我似乎无法找到解决方案。理想情况下，解决方案将是我可以执行以进行客户链接的存储过程。

-SQL Server 2008 R2标准64位

更新-------------------------------

我知道很难解释这个问题，所以我要怪。但从本质上讲，我希望能够链接具有相同identificationNumbers的所有客户，但是一个客户只能拥有多个identificationNumber。以示例1为例。1个A（1个为Personal_id_type_code，A为IdentificationNumber存在4个不同的客户。CU-1，CU-2，CU-3，CU-4。因此，它们可能是同一客户，在同一时间存在4个不同的时间带有不同客户ID的客户表。我们需要将它们与1个公共值关联。但是，CU-1具有2个其他标识，即使其中1个与其他3个标识（CU-2，CU-3，CU-4 ），他们不是同一位客户。因此，编号为2且编号为A的ID与CU-3（编号为B的ID 2）和CU-4的ID 2不匹配。 ，CU-1的ID 3和num A与CU-2的ID 3（其B）不匹配，因此根本不匹配。

下一个公共ID和num是2-b，它存在于CU-3和CU-4中。这两个客户实际上是相同的，原因都是ID 1-A和ID 2-B。ID4-C和ID 3-A无关，因为两个ID不同。从本质上讲，这意味着该客户具有4个ID I A，2 B，4 C和3A。因此，现在我们需要将此客户与customer表中的公共唯一值（guid）关联。

我希望我现在解释了这个非常复杂的问题。很难解释，因为这是一个非常独特的问题。

Answer 1

我对您的数据模型进行了一些更改，以使其变得更加明显。

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]