Поиск совпадающих записей на основе динамических столбцов

Ответ 1

Я скажу сразу, чтобы сэкономить ваше время:

• Мое решение использует динамический SQL. Michał Turczyn правильно отметил, что вы не можете избежать этого, в то время как имена сравниваемых столбцов хранятся в БД.
• В моем решении используются циклы. И я твердо верю, что вы не решите эту проблему с чистым SQL-запросом, который будет работать достаточно быстро на объявленный вами размер данных (таблицы имеют> 1M записей). Логика, которую вы описали, подразумевает итерации по своей природе - от большего набора совпадающих полей до нижнего набора. SQL как язык запросов не был предназначен для покрытия таких сложных сценариев. Вы можете попытаться решить свою проблему с помощью чистого SQL-запроса, но даже если вам удастся построить такой запрос, это будет очень сложно, сложно и неясно. Я не поклонник таких решений. Вот почему я даже не копаюсь в этом направлении.
• С другой стороны, мое решение не требует создания временных таблиц, что является преимуществом.

Учитывая это, мой подход справедлив:

1. Существует внешний цикл, который выполняет итерацию с самого большого набора совпадений (все соответствующие поля) до самого маленького набора совпадений (одно поле). На первой итерации, когда мы еще не знаем, сколько хранителей хранится в базе данных для домашнего животного, мы читаем и используем их все. На следующих итерациях мы уменьшаем количество использованных счетчиков на 1 (удаляя один с наивысшим приоритетом).

2. Внутренний цикл выполняет итерацию по текущему набору соответствий и создает WHERE, которое сравнивает поля между таблицей " Pets и Owners.

3. Текущий запрос выполняется, и если какой-либо владелец соответствует заданным критериям, мы прерываем внешний цикл.

Вот код, который реализует эту логику:

DECLARE @PetId INT = 2;

DECLARE @MatchersLimit INT;
DECLARE @OwnerID INT;

WHILE (@MatchersLimit IS NULL OR @MatchersLimit > 0) AND @OwnerID IS NULL
BEGIN

    DECLARE @CurrMatchFilter VARCHAR(max) = ''
    DECLARE @Field VARCHAR(30)
    DECLARE @MatchTo VARCHAR(30)
    DECLARE @CurrMatchersNumber INT = 0;

    DECLARE @GetMatchers CURSOR;
    IF @MatchersLimit IS NULL
        SET @GetMatchers = CURSOR FOR SELECT Field, MatchTo FROM temp_builder WHERE PetID = @PetId ORDER BY Priority ASC;
    ELSE
        SET @GetMatchers = CURSOR FOR SELECT TOP (@MatchersLimit) Field, MatchTo FROM temp_builder WHERE PetID = @PetId ORDER BY Priority ASC;

    OPEN @GetMatchers;
    FETCH NEXT FROM @GetMatchers INTO @Field, @MatchTo;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @CurrMatchFilter <> '' SET @CurrMatchFilter = @CurrMatchFilter + ' AND ';
        SET @CurrMatchFilter = @CurrMatchFilter + ('temp_pets.' + @Field + ' = ' + 'temp_owners.' + @MatchTo);
        FETCH NEXT FROM @GetMatchers INTO @field, @matchTo;
        SET @CurrMatchersNumber = @CurrMatchersNumber + 1;
    END
    CLOSE @GetMatchers;
    DEALLOCATE @GetMatchers;

    IF @CurrMatchersNumber = 0 BREAK;

    DECLARE @CurrQuery nvarchar(max) = N'SELECT @id = temp_owners.OwnerID FROM temp_owners INNER JOIN temp_pets ON (' + CAST(@CurrMatchFilter AS NVARCHAR(MAX)) + N') WHERE temp_pets.PetID = ' + CAST(@PetId AS NVARCHAR(MAX));
    EXECUTE sp_executesql @CurrQuery, N'@id int OUTPUT', @[email protected] OUTPUT;

    IF @MatchersLimit IS NULL
        SET @MatchersLimit = @CurrMatchersNumber - 1;
    ELSE
        SET @MatchersLimit = @MatchersLimit - 1;

END

SELECT @OwnerID AS OwnerID, @MatchersLimit + 1 AS Matched;

Требования к производительности

В этом подходе используются в основном 2 запроса:

1. SELECT Field, MatchTo FROM temp_builder WHERE PetID = @PetId;

   Вы должны добавить индекс PetID поля в temp_builder таблицы и этот запрос будет выполняться очень быстро.

2. SELECT @id = temp_owners.OwnerID FROM temp_owners INNER JOIN temp_pets ON (temp_pets.Document = temp_owners.Document AND temp_pets.OwnerName = temp_owners.Name AND temp_pets.Zip = temp_owners.Zip AND...) WHERE temp_pets.PetID = @PetId;

   Этот запрос выглядит страшно, потому что он объединяет две большие таблицы - temp_owners и temp_pets. Однако таблица temp_pets фильтруется столбец PetID который должен PetID только одну запись. Поэтому, если у вас есть индекс в столбце temp_pets.PetID (и вы должны, как этот столбец, как первичный ключ), запрос приведет к сканированию таблицы temp_owners. Такое сканирование не займет веков даже для таблицы с более 1 М строк. Если запрос все еще слишком медленный, вы можете рассмотреть возможность добавления индексов для столбцов таблицы temp_owners, которые используются в сопоставлениях (Addr, Zip и т.д.). Добавление индексов имеет недостатки, такие как большая база данных и более медленные операции вставки/обновления. Поэтому перед добавлением индексов в столбцы temp_owners проверьте скорость запроса на таблицу без индексов.

Ответ 2

Использование курсора, циклов и динамического SQL можно избежать, рассматривая поля, используемые для сравнения, как запись в бит, установленную для каждого питомца. Битовый набор (столбец FieldSetRank) вычисляется для каждого приоритета на основе записи бит (столбец ранжирования FieldRank).

Таблицы "Домашние животные" и "Владелец" должны быть отключены, чтобы поля и связанные с ними значения могли быть сопоставлены. Каждое из полей и значения, которые были сопоставлены, назначается соответствующему полевому регистру. Затем вычисляется новый набор бит на основе совпадающих значений (MatchSetRank). Возвращаются только записи, в которых сопоставленные наборы (MatchSetRank) равны требуемым наборам (FieldSetRank).

Запрос выполняет одно окончательное ранжирование, чтобы возвращать записи с самым высоким MatchSetRank (записи, которые сопоставляются с наибольшим количеством столбцов при сохранении критериев приоритета. Следующий T-SQL демонстрирует концепцию.

;WITH CTE_Builder
 AS
 (
     SELECT  [PetID]
            ,[Field]
            ,[Priority]
            ,[MatchTo]
            ,POWER(2, [Priority] - 1) AS [FieldRank] -- Define the field ranking as bit set numbered item.
            ,SUM(POWER(2, [Priority] - 1)) OVER (PARTITION BY [PetID] ORDER BY [Priority] ROWS UNBOUNDED PRECEDING) FieldSetRank -- Sum all the bit set IDs to define what constitutes a completed field set ordered by priority.
     FROM   temp_builder
 ),
CTE_PetsUnpivoted
AS
(   -- Unpivot pets table and assign Field Rank and Field Set Rank.
    SELECT   [PetsUnPivot].[PetID]
            ,[PetsUnPivot].[Field]
            ,[Builder].[MatchTo]
            ,[PetsUnPivot].[FieldValue]
            ,[Builder].[Priority]
            ,[Builder].[FieldRank]
            ,[Builder].[FieldSetRank]

    FROM 
       (
            SELECT [PetID], [Address], CAST([Zip] AS VARCHAR(100)) AS [Zip], [Country], [Document], [OwnerName]
            FROM temp_pets
        ) [Pets]
    UNPIVOT
       (FieldValue FOR Field IN 
          ([Address], [Zip], [Country], [Document], [OwnerName])
    ) AS [PetsUnPivot]
    INNER JOIN [CTE_Builder] [Builder] ON [PetsUnPivot].PetID = [Builder].PetID AND [PetsUnPivot].Field = [Builder].Field
),
CTE_Owners
AS
(
    -- Unpivot Owners table and join with unpivoted Pets table on field name and field value.  
    -- Next assign Pets field rank then calculated the field set rank (MatchSetRank) based on actual matches made.
    SELECT   [OwnersUnPivot].[OwnerID]
            ,[Pets].[PetID]
            ,[OwnersUnPivot].[Field]
            ,[Pets].Field AS [PetField]
            ,[Pets].FieldValue as PetFieldValue
            ,[OwnersUnPivot].[FieldValue]
            ,[Pets].[Priority]
            ,[Pets].[FieldRank]
            ,[Pets].[FieldSetRank]
            ,SUM([FieldRank]) OVER (PARTITION BY [Pets].[PetID], [OwnersUnPivot].[OwnerID] ORDER BY [Pets].[Priority] ROWS UNBOUNDED PRECEDING) MatchSetRank
    FROM 
       (
            SELECT [OwnerID], [Addr], CAST([Zip] AS VARCHAR(100)) AS [Zip], [Country], [Document], [Name]
            FROM temp_owners
        ) [Owners]
    UNPIVOT
       (FieldValue FOR Field IN 
          ([Addr], [Zip], [Country], [Document], [Name])
    ) AS [OwnersUnPivot]
    INNER JOIN [CTE_PetsUnpivoted] [Pets] ON [OwnersUnPivot].[Field] = [Pets].[MatchTo] AND [OwnersUnPivot].[FieldValue] = [Pets].[FieldValue]
),
CTE_FinalRanking
AS
(
    SELECT   [PetID]
            ,[OwnerID]
            -- -- Calculate final rank, if multiple matches have the same rank then multiple rows will be returned per pet. 
            -- Change the "RANK()" function to "ROW_NUMBER()" to only return on result per pet.
            ,RANK() OVER (PARTITION BY [PetID] ORDER BY [MatchSetRank] DESC) AS [FinalRank] 
    FROM    CTE_Owners
    WHERE   [FieldSetRank] = [MatchSetRank] -- Only return records where the field sets calculated based on 
                                            -- actual matches is equal to desired field set ranks. This will 
                                            -- eliminate matches where the number of fields that meets the 
                                            -- criteria is the same but does not meet priority requirements. 
)
SELECT   [PetID]
        ,[OwnerID]
FROM    CTE_FinalRanking
WHERE   [FinalRank] = 1

Ответ 3

Это довольно большая задача, чтобы выполнить... Вот как я это сделал:

Во-первых, вам нужно добавить таблицу, в которой будут содержаться semi-, where предложения, то есть условия, готовые к использованию на temp_builder таблицы temp_builder. Кроме того, поскольку у вас есть 5 столбцов, я предположил, что может быть не более 5 условий. Здесь создание таблицы:

CREATE TABLE [dbo].[temp_builder_with_where](
    [petid] [int] NULL,
    [priority1] [bit] NULL,
    [priority2] [bit] NULL,
    [priority3] [bit] NULL,
    [priority4] [bit] NULL,
    [priority5] [bit] NULL,
    [whereClause] [varchar](200) NULL
) 
--it good to create index, for better performance
create clustered index idx on [temp_builder_with_where]([petid])

insert into temp_builder_with_where
select petid,[priority1],[priority2],[priority3],[priority4],[priority5],
         '[pets].' + CAST(field as varchar(100)) + ' = [owners].' + CAST(matchto as varchar(100)) [whereClause]
from (
select petid, field, matchto, [priority],
        1 Priority1,
        case when [priority] > 1 then 1 else 0 end Priority2,
        case when [priority] > 2 then 1 else 0 end Priority3,
        case when [priority] > 3 then 1 else 0 end Priority4,
        case when [priority] > 4 then 1 else 0 end Priority5       
from temp_builder) [builder]

Теперь мы пройдем через эту таблицу. Вы сказали, что в этой таблице содержится 8000 строк, поэтому я выбрал другой способ: динамический запрос теперь будет вставлять результаты только для одного petid.

Для этого нам нужна таблица для хранения наших результатов:

CREATE TABLE [dbo].[TableWithNewId](
    [petid] [int] NULL,
    [ownerid] [int] NULL,
    [priority] [int] NULL
)

Теперь динамический SQL используется для операторов insert:

declare @query varchar(1000) = ''
declare @i int, @max int
set @i = 1
select @max = MAX(petid) from temp_builder_with_where

while @i <= @max
begin

    set @query = ''

    select @query = @query + whereClause1 + whereClause2 + whereClause3 + whereClause4 + whereClause5 + ' union all ' from (
    select 'insert into [MY_DATABASE].dbo.TableWithNewId  select ' + CAST(petid as varchar(3)) + ' [petid], [owners].ownerid, 1 [priority] from temp_pets [pets], temp_owners [owners] where (' + [where_petid] + [where1] + ')' [whereClause1],
           case when [where2] is null then '' else ' union all select ' + CAST(petid as varchar(3)) + ' [petid], [owners].ownerid, 2 [priority] from temp_pets [pets], temp_owners [owners] where (' + [where_petid] + [where2] + ')' end [whereClause2], 
           case when [where3] is null then '' else ' union all select ' + CAST(petid as varchar(3)) + ' [petid], [owners].ownerid, 3 [priority] from temp_pets [pets], temp_owners [owners] where (' + [where_petid] + [where3] + ')' end [whereClause3], 
           case when [where4] is null then '' else ' union all select ' + CAST(petid as varchar(3)) + ' [petid], [owners].ownerid, 4 [priority] from temp_pets [pets], temp_owners [owners] where (' + [where_petid] + [where4] + ')' end [whereClause4], 
           case when [where5] is null then '' else ' union all select ' + CAST(petid as varchar(3)) + ' [petid], [owners].ownerid, 5 [priority] from temp_pets [pets], temp_owners [owners] where (' + [where_petid] + [where5] + ')' end [whereClause5]
    from (
            select petid, 'petid = ' + CAST(petid as nvarchar(3)) [where_petid],
               (select ' and ' + whereClause from temp_builder_with_where where petid = t.petid and priority1 = 1 for xml path(''),type).value('(.)[1]', 'varchar(500)') [where1],
               (select ' and ' + whereClause from temp_builder_with_where where petid = t.petid and priority2 = 1 for xml path(''),type).value('(.)[1]', 'varchar(500)') [where2],
               (select ' and ' + whereClause from temp_builder_with_where where petid = t.petid and priority3 = 1 for xml path(''),type).value('(.)[1]', 'varchar(500)') [where3],
               (select ' and ' + whereClause from temp_builder_with_where where petid = t.petid and priority4 = 1 for xml path(''),type).value('(.)[1]', 'varchar(500)') [where4],
               (select ' and ' + whereClause from temp_builder_with_where where petid = t.petid and priority5 = 1 for xml path(''),type).value('(.)[1]', 'varchar(500)') [where5]
       from temp_builder_with_where [t]
       where petid = @i
        group by petid
    ) a
    ) a
    --remove last union all
    set @query = left(@query, len(@query) - 10)
    exec (@query)

    set @i = @i + 1

end

Имейте в виду, что вы должны заменить [MY_DATABASE] в вышеуказанном коде своим именем базы данных. Из ваших данных примера это будет результат запроса select * from TableWithNewId:

PetId|OwnerId|Priority
1    |6      |4
2    |4      |2
2    |4      |3
3    |1      |1
3    |2      |1
3    |3      |1
3    |4      |1
3    |5      |1
3    |6      |1

Исходя из этого результата, теперь вы можете назначить OwnerId для PetId на основе самого низкого приоритета (ну, вы не сказали, как обращаться с ситуацией, где найдено более одного OwnerId для одного и того же приоритета).

Ответ 4

Я не уверен, правильно ли я получил конечный результат или нет, но я предлагаю использовать пару общих табличных выражений для создания пакета операторов обновления с использованием динамического SQL (я боюсь, что это невозможно сделать без динамического SQL), а затем выполнить их с помощью Exec(sql).

Преимущество такого подхода состоит в том, что он не содержит циклов или курсоров.

Каждый оператор обновления, который я генерирую, использует inner join между домашними животными и таблицами владельцев, обновляя идентификатор владельца таблицы домашних животных с идентификатором владельца таблицы владельцев, используя сопоставления из таблицы строителя в качестве основы для предложения on.
Первый cte отвечает за создание предложения on из таблицы строителя, а второй отвечает за создание операторов обновления.
Наконец, я выбираю все операторы SQL из второго CTE в одну переменную nvarchar(max) и выполняю ее.

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

Итак, сначала нужно объявить переменную для хранения генерируемых операторов обновления:

DECLARE @Sql nvarchar(max) = ''

Теперь первый CTE использует cross apply with stuff и for xml для генерации предложения on для каждой пары petId и Priority:

;WITH OnClauseCTE AS
(
SELECT DISTINCT PetId, Priority, OnClause
FROM temp_builder t0
CROSS APPLY
(
    SELECT STUFF (
    (  
        SELECT ' AND p.'+ Field +' = o.'+ MatchTo
        FROM temp_builder t1
        WHERE PetID = t0.PetId
        AND Priority <= t0.Priority
        FOR XML PATH('')  
    )
    , 1, 5, '') As OnClause
) onClauseGenerator
)

Второй CTE генерирует один оператор UPDATE для каждой petId и Priority:

, UpdateStatementCTE AS
(
    SELECT  PetId,
            Priority,
            'UPDATE p 
            SET OwnerID = o.OwnerID 
            FROM temp_pets p 
            INNER JOIN temp_owners o ON ' + OnClause + ' 
            WHERE p.PetId = '+ CAST(PetId as varchar(10)) +'
            AND p.OwnerID IS NULL; -- THIS IS CRITICAL!
            ' AS SQL
    FROM OnClauseCTE
)

И, наконец, создание единой партии команд обновления из UpdateStatementCTE:

SELECT @Sql = @Sql + SQL
FROM UpdateStatementCTE    
ORDER BY PetId, Priority DESC -- ORDER BY Priority is CRITICAL!

order by PetId строго предназначен для чтения, когда вы печатаете содержимое @Sql. Тем не менее, Priority DESC часть Priority DESC в предложении order by имеет решающее значение, так как мы хотим сначала выполнить наивысший приоритет, а самый низкий приоритет - последним.

Теперь @Sql содержит это (сокращенное):

UPDATE p 
SET OwnerID = o.OwnerID 
FROM temp_pets p 
INNER JOIN temp_owners o ON p.Address = o.Addr AND p.Zip = o.Zip AND p.Country = o.Country AND p.OwnerName = o.Name 
WHERE p.PetId = 1
AND p.OwnerID IS NULL;

...

UPDATE p 
SET OwnerID = o.OwnerID 
FROM temp_pets p 
INNER JOIN temp_owners o ON p.OwnerName = o.Name 
WHERE p.PetId = 1
AND p.OwnerID IS NULL;

...

UPDATE p 
SET OwnerID = o.OwnerID 
FROM temp_pets p 
INNER JOIN temp_owners o ON p.OwnerName = o.Name AND p.Document = o.Document 
WHERE p.PetId = 2
AND p.OwnerID IS NULL;

...

UPDATE p 
SET OwnerID = o.OwnerID 
FROM temp_pets p 
INNER JOIN temp_owners o ON p.Country = o.Country 
WHERE p.PetId = 3
AND p.OwnerID IS NULL;

Как вы можете видеть, каждый оператор обновления представлен в таблице построителя и будет изменять только идентификатор владельца, если предыдущий оператор обновления еще не был из-за части AND p.OwnerID IS NULL в предложении where.

После запуска пакета операторов обновления таблица temp_pets выглядит так:

PetID   Address         Zip     Country     Document    OwnerName   OwnerID     Field1  Field2
1       123 5th st      12345   US          test.csv    John        5           NULL    NULL
2       234 6th st      23456   US          a.csv       Alex        6           NULL    NULL
3       345 7th st      34567   US          b.csv       Mike        1           NULL    NUL

Вы можете увидеть живую демонстрацию в реестре.

Однако учтите, что чем меньше условий у вас есть больше записей, они могут вернуться обратно из соединения, сделав обновление более вероятным неточным. Например, для PetId 3 у меня есть OwnerId 1, так как единственное, что мне пришлось сопоставить с записями, это столбец " Country, то есть фактически это может быть каждый OwnerId в этих образцовых данных, поскольку каждый имеет то же значение, что и US в Столбец Country.
Согласно следующим правилам, я не могу с этим поделать.

Ответ 5

Это можно обойти без динамических sql или циклов. Ключевым моментом является то, что столбцы, используемые для соответствия домашним животным и владельцам, являются статическими. Только приоритеты динамичны. Однако производительность во многом зависит от ваших данных. Вы должны сами проверить это и подумать, как вы думаете, лучший подход.

Решение ниже в основном находит всех владельцев, которые соответствуют любому данному питомцу. Затем владельцы фильтруются только для владельцев, которые соответствуют приоритету 1, или 1 и 2, или 1 и 2 и 3 и т.д. Наконец, найдено "лучшее" совпадающих владельцев, а таблица домашних животных обновляется с этим значением,

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

-- We start off by converting the priority values into int values that are suitable to add up to a bit array
-- I'll save those in a #Temp table to cut that piece of logic out of the final query
IF EXISTS(SELECT 1 FROM #TempBuilder)
BEGIN
    DROP TABLE #TempBuilder
END
SELECT 
    PetID, Field, MatchTo, 
    CASE [Priority] 
    WHEN 1 THEN 16 -- Priority one goes on the 16-bit (10000)
    WHEN 2 THEN 8 -- Priority two goes on the 8-bit (01000)
    WHEN 3 THEN 4 -- Priority three goes on the 4-bit (00100)
    WHEN 4 THEN 2 -- Priority four goes on the 2-bit (00010)
    WHEN 5 THEN 1 END AS [Priority] -- Priority five goes on the 1-bit (00001)
INTO #TempBuilder
FROM dbo.temp_builder;

-- Then we pivot the match priorities to be able to join them on our pets
WITH PivotedMatchPriorities AS (
    SELECT
        PetId,
        [Address], [Zip], [Country], [OwnerName], [Document]
    FROM (SELECT PetId, Field, [Priority] FROM #TempBuilder) tb
        PIVOT 
        (
            SUM([Priority])
            FOR [Field] IN ([Address], [Zip], [Country], [OwnerName], [Document])
        )
        AS PivotedMatchPriorities
),
-- Next we get (for each pet) all owners with ANY matching value
-- We want to filter the matching owners to find these that match priorities 1 (priority sum 10000, i.e. 16), 
    --- or match priorities 1 & 2 (priority sum 11000, i.e. 24)
    --- or match priorities 1 & 2 & 3 (priority sum 11100, i.e. 28)
    --- etc.
MatchingOwners AS (
    SELECT o.*,
        p.PetID,
        pmp.[Address] AS AddressPrio,
        pmp.Country AS CountryPrio,
        pmp.Zip AS ZipPrio,
        pmp.OwnerName AS OwnerPrio,
        pmp.Document AS DocumentPrio,
        CASE WHEN o.Addr = p.[Address] THEN ISNULL(pmp.[Address],0) ELSE 0 END
        + CASE WHEN o.Zip = p.Zip THEN ISNULL(pmp.Zip,0) ELSE 0 END
        + CASE WHEN o.Country = p.Country THEN ISNULL(pmp.Country,0) ELSE 0 END
        + CASE WHEN o.Document = p.Document THEN ISNULL(pmp.[Document],0) ELSE 0 END
        + CASE WHEN o.[Name] = p.OwnerName THEN ISNULL(pmp.OwnerName,0) ELSE 0 END AS MatchValue -- Calculate a match value for each matching owner
    FROM dbo.temp_pets p
        INNER JOIN dbo.temp_owners o 
            ON p.[Address] = o.Addr
            OR p.Country = o.Country
            OR p.Document = o.Document
            OR p.OwnerName = o.[Name]
            OR p.Zip = o.Zip
        INNER JOIN PivotedMatchPriorities pmp ON pmp.PetId = p.PetId
),
-- Now we can get all owners that match the pet, along with a match value for each owner.
-- We want to rank the matching owners for each pet to allow selecting the best ranked owner
-- Note: In the demo data there are multiple owners that match petId 3 equally well. We'll pick a random one in such cases.
RankedValidMatches AS (
    SELECT 
        PetID,
        OwnerID,
        MatchValue,
        ROW_NUMBER() OVER (PARTITION BY PetID ORDER BY MatchValue DESC) AS OwnerRank
    FROM MatchingOwners
    WHERE MatchValue IN (16, 24, 28, 30, 31)
)
-- Finally we can get the best valid match per pet
--SELECT * FROM RankedValidMatches WHERE OwnerRank = 1
-- Or we can update our pet table to reflect our results
UPDATE dbo.temp_pets
SET OwnerID = rvm.OwnerID
FROM dbo.temp_pets tp
    INNER JOIN RankedValidMatches rvm ON rvm.PetID = tp.PetID AND rvm.OwnerRank = 1

Ответ 6

Следующий подход основан на том, что количество различных комбинаций для выбора и упорядочения столбцов, которые должны совпадать, ограничено и, вероятно, будет намного меньше количества записей. С 5 столбцами общее количество комбинаций составляет 325, но поскольку маловероятно, что используется всякая возможная комбинация, фактическое число будет, вероятно, меньше 100. По сравнению с количеством записей (OP упоминается> 1M), он платит чтобы попытаться объединить домашних животных, которые используют одну и ту же комбинацию столбцов.

Характеристики сценария SQL ниже:

• Нет динамического SQL.
• Петли, но не курсоры; число итераций ограничено и не увеличивается пропорционально количеству записей.
• Создает две (индексированные) вспомогательные таблицы. (Не стесняйтесь делать их временными таблицами или переменными таблицы.) Это значительно ускоряет процесс сопоставления (INNER JOIN), но при этом некоторые накладные расходы требуют заполнения таблиц.
• Только простые конструкторы SQL (нет опорных точек, не заполнены FOR XML, а не CTE).
• Используется только для индексов в ключевых столбцах (PetID, OwnerID), столбце Priority и столбцах в вспомогательных таблицах. Не нужны индексы по адресу, почтовому индексу, стране, документу, имени.

На первый взгляд запрос выглядит как полный избыток (47 операторов SQL, выполненных на небольшом количестве выборочных данных, предложенных OP), но для больших таблиц преимущество должно стать очевидным. Худшая временная сложность должна быть O (n log n), что намного лучше, чем многие альтернативы. Но, конечно, ему все еще нужно проявлять себя на практике; Я еще не тестировал его с большими наборами данных.

Сценарий: http://sqlfiddle.com/#!18/53320/1

-- Adding indexes to OP tables to optimize the queries that follow.
CREATE INDEX IX_PetID ON temp_builder (PetID)
CREATE INDEX IX_Priority ON temp_builder (Priority)
CREATE INDEX IX_PetID ON temp_pets (PetID)
CREATE INDEX IX_OwnerID ON temp_owners (OwnerID)

-- Helper table for pets. Each column has its own index.
CREATE TABLE PetKey (
    PetID int NOT NULL PRIMARY KEY CLUSTERED,
    KeyNames varchar(200) NOT NULL INDEX IX_KeyNames NONCLUSTERED,
    KeyValues varchar(900) NOT NULL INDEX IX_KeyValues NONCLUSTERED
)

-- Helper table for owners. Each column has its own index.
CREATE TABLE OwnerKey (
    OwnerID int NOT NULL PRIMARY KEY CLUSTERED,
    KeyValues varchar(900) NULL INDEX IX_KeyValues NONCLUSTERED
)

-- For every pet, create a record in table PetKey.
-- (Unless the pet already belongs to someone.)
INSERT INTO PetKey (PetID, KeyNames, KeyValues)
SELECT PetID, '', ''
FROM temp_pets
WHERE OwnerID IS NULL

-- For every owner, create a record in table OwnerKey.
INSERT INTO OwnerKey (OwnerID, KeyValues)
SELECT OwnerID, ''
FROM temp_owners

-- Populate columns KeyNames and KeyValues in table PetKey.
-- Lowest priority (i.e. highest number in column Priority) comes first.
-- We use CHAR(1) as a separator character; anything will do as long as it does not occur in any column values.
-- Example: when a pet has address as prio 1, zip as prio 2, then:
--    KeyNames = 'Zip' + CHAR(1) + 'Address' + CHAR(1)
--    KeyValues = '12345' + CHAR(1) + 'John' + CHAR(1)
-- NULL is replaced by CHAR(2); can be any value as long as it does not match any owner value.
DECLARE @priority int = 1
WHILE EXISTS (SELECT * FROM temp_builder WHERE Priority = @priority)
BEGIN
    UPDATE pk
    SET KeyNames = b.Field + CHAR(1) + KeyNames,
        KeyValues = ISNULL(CASE b.Field
                               WHEN 'Address' THEN p.Address
                               WHEN 'Zip' THEN CAST(p.Zip AS varchar)
                               WHEN 'Country' THEN p.Country
                               WHEN 'Document' THEN p.Document
                               WHEN 'OwnerName' THEN p.OwnerName
                           END, CHAR(2)) +
                    CHAR(1) + KeyValues
    FROM PetKey pk
    INNER JOIN temp_pets p ON p.PetID = pk.PetID
    INNER JOIN temp_builder b ON b.PetID = pk.PetID
    WHERE b.Priority = @priority

    SET @priority = @priority + 1
END

-- Loop through all distinct key combinations.
DECLARE @maxKeyNames varchar(200), @namesToAdd varchar(200), @index int
SELECT @maxKeyNames = MAX(KeyNames) FROM PetKey
WHILE @maxKeyNames <> '' BEGIN
    -- Populate column KeyValues in table OwnerKey.
    -- The order of the values is determined by the column names listed in @maxKeyNames.
    UPDATE OwnerKey
    SET KeyValues = ''

    SET @namesToAdd = @maxKeyNames
    WHILE @namesToAdd <> '' BEGIN
        SET @index = CHARINDEX(CHAR(1), @namesToAdd)

        UPDATE ok
        SET KeyValues = KeyValues +
                        CASE LEFT(@namesToAdd, @index - 1)
                            WHEN 'Address' THEN o.Addr
                            WHEN 'Zip' THEN CAST(o.Zip AS varchar)
                            WHEN 'Country' THEN o.Country
                            WHEN 'Document' THEN o.Document
                            WHEN 'OwnerName' THEN o.Name
                        END +
                        CHAR(1)
        FROM OwnerKey ok
        INNER JOIN temp_owners o ON o.OwnerID = ok.OwnerID

        SET @namesToAdd = SUBSTRING(@namesToAdd, @index + 1, 200)
    END

    -- Match pets with owners, based on their KeyValues.
    UPDATE p
    SET OwnerID = (SELECT TOP 1 ok.OwnerID FROM OwnerKey ok WHERE ok.KeyValues = pk.KeyValues)
    FROM temp_pets p
    INNER JOIN PetKey pk ON pk.PetID = p.PetID
    WHERE pk.KeyNames = @maxKeyNames

    -- Pets that were successfully matched are removed from PetKey.
    DELETE FROM pk
    FROM PetKey pk
    INNER JOIN temp_pets p ON p.PetID = pk.PetID
    WHERE p.OwnerID IS NOT NULL

    -- For pets with no match, strip off the first (lowest priority) name and value.
    SET @namesToAdd = SUBSTRING(@maxKeyNames, CHARINDEX(CHAR(1), @maxKeyNames) + 1, 200)

    UPDATE pk
    SET KeyNames = @namesToAdd,
        KeyValues = SUBSTRING(KeyValues, CHARINDEX(CHAR(1), KeyValues) + 1, 900)
    FROM PetKey pk
    INNER JOIN temp_pets p ON p.PetID = pk.PetID
    WHERE pk.KeyNames = @maxKeyNames

    -- Next key combination.    
    SELECT @maxKeyNames = MAX(KeyNames) FROM PetKey
END

Ответ 7

Я написал другую версию, используя UNPIVOT, но ранжируя строки и фильтруя их более простым способом

;with
-- r: rules table
r as (select * from temp_builder),
-- o0: owners table with all fields unpivotable (varchar)
o0 as (SELECT [OwnerID], [Addr], CAST([Zip] AS VARCHAR(100)) AS [Zip], [Country], [Document], [Name] FROM temp_owners ),
-- o: owners table unpivoted
o as (
    SELECT * FROM o0 
    UNPIVOT (FieldValue FOR Field IN ([Addr], [Zip], [Country], [Document], [Name])) AS p
),
-- p0: pets table with all fields unpivotable (varchar)
p0 as (SELECT [PetID], [Address], CAST([Zip] AS VARCHAR(100)) AS [Zip], [Country], [Document], [OwnerName] FROM temp_pets),
-- p: petstable unpivoted
p as (
    SELECT * FROM p0
    UNPIVOT (FieldValue FOR Field IN ([Address], [Zip], [Country], [Document], [OwnerName])) AS p
),
-- t: join up all data and keep only matching priority
d as (
    select petid, ownerid, priority 
    from (
        select r.*, o.ownerid, ROW_NUMBER() over (partition by r.petid, o.ownerid order by r.petid, o.ownerid, priority) calc_priority
        from r
        join p on (r.field = p.field) and (p.petid = r.petid)
        join o on (r.matchto = o.field) and (p.fieldvalue=o.fieldvalue) 
    ) x
    where calc_priority=priority
),
-- g: group by the matching rows to know the best priority reached for each pet
g as (
    select petid, max(priority) max_priority
    from d
    group by petid
)
-- output only the rows with best priority
select d.*
from d
join g on d.petid = g.petid and d.priority = g.max_priority
order by petid, ownerid, priority

Эта версия не более эффективна, чем @EdmondQuinton одна, (я проголосовал за него), мой 5% медленнее, но я думаю, что это проще понять и поддерживать для пользователей, не являющихся профессионалами.

Ответ 8

Я бы взял несколько иной подход, вместо того чтобы хранить столбцы для соответствия, вы могли бы сохранить запрос, который должен быть выполнен:

create table builder
(
    PetID int not null,
    Query varchar(max)
)

INSERT INTO builder
VALUES (1, 'SELECT TOP 1 *
FROM pets
INNER JOIN Owners
    ON Owners.Name = pets.OwnerName 
WHERE petId = 1
ORDER BY 
    CASE WHEN Owners.Country = pets.Country THEN 0 ELSE 1 END,
    CASE WHEN Owners.Zip = pets.Zip THEN 0 ELSE 1 END,
    CASE WHEN Owners.Addr = pets.Address THEN 0 ELSE 1 END'),
(2, 'SELECT TOP 1 *
FROM pets
INNER JOIN Owners
    ON Owners.Name = pets.OwnerName 
WHERE petId = 2
ORDER BY 
    CASE WHEN Owners.Document = pets.Document THEN 0 ELSE 1 END,
    CASE WHEN Owners.Name = pets.OwnerName THEN 0 ELSE 1 END,
    CASE WHEN Owners.Zip = pets.Zip THEN 0 ELSE 1 END'),
(3, 'SELECT TOP 1 *
FROM pets
INNER JOIN Owners
    ON Owners.Name = pets.OwnerName 
WHERE petId = 3
ORDER BY 
    CASE WHEN Owners.Country = pets.Country THEN 0 ELSE 1 END
')

create table pets
(
    PetID int null,
    Address varchar(100) null,
    Zip int null,
    Country varchar(100) null,
    Document varchar(100) null,
    OwnerName varchar(100) null,
    OwnerID int null,
    Field1 bit null,
    Field2 bit null
)

insert into pets values
(1, '123 5th st', 12345, 'US', 'test.csv', 'John', NULL, NULL, NULL),
(2, '234 6th st', 23456, 'US', 'a.csv', 'Alex', NULL, NULL, NULL),
(3, '345 7th st', 34567, 'US', 'b.csv', 'Mike', NULL, NULL, NULL)

create table owners
(
    OwnerID int null,
    Addr varchar(100) null,
    Zip int null,
    Country varchar(100) null,
    Document varchar(100) null,
    Name varchar(100) null,
    OtherField bit null,
    OtherField2 bit null,
)

insert into owners values
(1, '456 8th st',  45678, 'US', 'c.csv', 'Mike',  NULL, NULL),
(2, '678 9th st',  45678, 'US', 'b.csv', 'John',  NULL, NULL),
(3, '890 10th st', 45678, 'US', 'b.csv', 'Alex',  NULL, NULL),
(4, '901 11th st', 23456, 'US', 'b.csv', 'Alex',  NULL, NULL),
(5, '234 5th st',  12345, 'US', 'b.csv', 'John',  NULL, NULL),
(6, '123 5th st',  45678, 'US', 'a.csv', 'John',  NULL, NULL)

Теперь, чтобы найти подходящего владельца для определенного домашнего животного, вы просто найдете запрос из таблицы и выполните его:

DECLARE @query varchar(max)
SELECT TOP 1 @query = query
FROM builder
WHERE petId =1

EXEC (@query)

Ответ 9

Это ответ, который строго решает вашу проблему, учитывая это

• Следуя правилам, которые вы не предлагали, ни циклы, ни курсоры, ни динамические sql
• А также строго учитывая вашу проблему, поэтому это не общее решение, оно очень специфично для вашей проблемы и столбцов, которые у вас есть.

TestData

declare @Pets table 
(
    PetID int null,
    Address varchar(100) null,
    Zip int null,
    Country varchar(100) null,
    Document varchar(100) null,
    OwnerName varchar(100) null,
    OwnerID int null,
    Field1 bit null,
    Field2 bit null
)

insert into @Pets values
(1, '123 5th st', 12345, 'US', 'test.csv', 'John', NULL, NULL, NULL),
(2, '234 6th st', 23456, 'US', 'a.csv', 'Alex', NULL, NULL, NULL),
(3, '345 7th st', 34567, 'US', 'b.csv', 'Mike', NULL, NULL, NULL)

declare @owners table
(
    OwnerID int null,
    Addr varchar(100) null,
    Zip int null,
    Country varchar(100) null,
    Document varchar(100) null,
    Name varchar(100) null,
    OtherField bit null,
    OtherField2 bit null
)

insert into @owners values
(1, '456 8th st',  45678, 'US', 'c.csv', 'Mike',  NULL, NULL),
(2, '678 9th st',  45678, 'US', 'b.csv', 'John',  NULL, NULL),
(3, '890 10th st', 45678, 'US', 'b.csv', 'Alex',  NULL, NULL),
(4, '901 11th st', 23456, 'US', 'b.csv', 'Alex',  NULL, NULL),
(5, '234 5th st',  12345, 'US', 'b.csv', 'John',  NULL, NULL),
(6, '123 5th st',  45678, 'US', 'a.csv', 'John',  NULL, NULL)

declare @builder table  
(
    PetID int not null,
    Field varchar(30) not null,
    MatchTo varchar(30) not null,
    Priority int not null
)

insert into @builder values
(1,'Address', 'Addr',4),
(1,'Zip', 'Zip', 3),
(1,'Country', 'Country', 2),
(1,'OwnerName', 'Name',1),
(2,'Zip', 'Zip',3),
(2,'OwnerName','Name', 2),
(2,'Document', 'Document', 1),
(3,'Country', 'Country', 1)

Код, который решает проблему

select distinct p.PetID, min(o.OwnerID) as ownerID from @pets p
inner join @builder b on p.PetID = b.PetID
inner join @owners o on 
( 
   (case when b.Field = 'Address' and b.Priority = 1 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 1 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 1 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 1 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 1 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 1 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 1 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 1 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 1 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 1 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 2 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 2 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 2 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 2 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 2 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 2 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 2 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 2 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 2 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 2 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 3 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 3 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 3 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 3 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 3 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 3 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 3 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 3 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 3 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 3 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 4 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 4 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 4 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 4 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 4 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 4 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 4 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 4 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 4 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 4 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 5 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 5 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 5 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 5 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 5 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 5 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 5 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 5 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 5 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 5 then o.Document else '-1' end)                    
)
group by p.PetID

union
--------------------------

select distinct p.PetID, min(o.OwnerID) as ownerID from @pets p
inner join @builder b on p.PetID = b.PetID
inner join @owners o on 
( 
   (case when b.Field = 'Address' and b.Priority = 1 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 1 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 1 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 1 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 1 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 1 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 1 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 1 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 1 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 1 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 2 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 2 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 2 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 2 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 2 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 2 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 2 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 2 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 2 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 2 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 3 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 3 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 3 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 3 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 3 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 3 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 3 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 3 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 3 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 3 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 4 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 4 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 4 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 4 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 4 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 4 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 4 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 4 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 4 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 4 then o.Document else '-1' end)                    
)
group by p.PetID

union
--------------------------

select distinct p.PetID, min(o.OwnerID) as ownerID from @pets p
inner join @builder b on p.PetID = b.PetID
inner join @owners o on 
( 
   (case when b.Field = 'Address' and b.Priority = 1 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 1 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 1 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 1 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 1 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 1 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 1 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 1 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 1 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 1 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 2 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 2 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 2 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 2 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 2 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 2 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 2 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 2 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 2 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 2 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 3 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 3 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 3 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 3 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 3 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 3 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 3 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 3 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 3 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 3 then o.Document else '-1' end)                    
)
group by p.PetID

union
------------------------

select distinct p.PetID, min(o.OwnerID) as ownerID from @pets p
inner join @builder b on p.PetID = b.PetID
inner join @owners o on 
( 
   (case when b.Field = 'Address' and b.Priority = 1 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 1 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 1 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 1 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 1 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 1 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 1 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 1 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 1 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 1 then o.Document else '-1' end)                    
)
AND
( 
   (case when b.Field = 'Address' and b.Priority = 2 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 2 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 2 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 2 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 2 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 2 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 2 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 2 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 2 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 2 then o.Document else '-1' end)                    
)
group by p.PetID

union
------------------------

select distinct p.PetID, min(o.OwnerID) as ownerID from @pets p
inner join @builder b on p.PetID = b.PetID
inner join @owners o on 
( 
   (case when b.Field = 'Address' and b.Priority = 1 then p.Address else '0' end) = (case when b.MatchTo = 'Addr' and b.Priority = 1 then o.Addr else '-1' end)                  
or (case when b.Field = 'Zip' and b.Priority = 1 then p.Zip else '0' end) = (case when b.MatchTo = 'Zip' and b.Priority = 1 then o.Zip else '-1' end)                    
or (case when b.Field = 'Country' and b.Priority = 1 then p.Country else '0' end) = (case when b.MatchTo = 'Country' and b.Priority = 1 then o.Country else '-1' end)                    
or (case when b.Field = 'OwnerName' and b.Priority = 1 then p.OwnerName else '0' end) = (case when b.MatchTo = 'Name' and b.Priority = 1 then o.Name else '-1' end)                  
or (case when b.Field = 'Document' and b.Priority = 1 then p.Document else '0' end) = (case when b.MatchTo = 'Document' and b.Priority = 1 then o.Document else '-1' end)                    
)
group by p.PetID

Результат

PetID   OwnerID
1       2
2       6
3       1

Ответ 10

Если вы ищете простое решение без объединения, цикл или курсор или динамический SQL ниже, запрос работает нормально.

SQL Fiddle: http://sqlfiddle.com/#!18/10982/41

select PetID ,COALESCE(
 (select  top 1 OwnerID from temp_owners
     where Zip = pets.Zip 
     and Name = pets.OwnerName
     and Document = pets.Document) ,
     (select top 1 OwnerID from temp_owners where
         Name = pets.OwnerName 
         and Document = pets.Document)  ,
         (select top 1 OwnerID from temp_owners where
          Document = pets.Document)  ) OwnerId
       from 
temp_pets pets

Результат:

PetID   OwnerId
1       (null)
2       6
3       2