Сравнение 2 или более значений столбцов в произвольном порядке

Мне нужен быстрый способ сравнить 2 или более значений из разных таблиц, где заказы произвольно хранятся в sql server. Данные поступают от третьей стороны, которая не будет меняться.

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

table1
i    j   other columns...
1    2   ...


table2
i    j   other columns
2    1   ...
1    2   ...

прямо сейчас для 2 я делаю запрос объединения для покрытия обоих направлений (i=i, j=j / i=j, j=i) . но если вы расширитесь до 3, это 9 возможных ордеров.

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.i = Table2.i AND Table1.j = Table2.j

UNION

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.i = Table2.j AND Table1.j = Table2.i

есть ли способ упорядочить данные, возвращаемые из первых двух столбцов, перед сравнением, чтобы не создавать все объединения?

1 ответ

  1. Изменить: новый подход xml

    Интересно, как работает этот подход:

    select  *, cast(    '<c>' + cast(i as varchar) + '</c>' +
                        '<c>' + cast(j as varchar) + '</c>' +
                        '<c>' + cast(k as varchar) + '</c>'
                as xml).query('for $a in /c order by $a return concat($a, "/")').value('.', 'varchar(100)')
    from    @Table1 o
    

    Это может быть обернуто в функцию и ссылаться в сохраненном столбце… который должен масштабироваться Очень хорошо для вас:

    create table dbo.Table1 (pk int identity(1,1) primary key, i int, j int, k int);
    insert into dbo.Table1
        values(1, 2, 3), (3, 1, 2), (4, 5, 6), (9,9,9);
    go
    
    create function dbo.fn_GenerateCompare(@i int, @j int, @k int)
    returns varchar(100)
    with schemabinding
    as
    begin
    return 
    (
        select cast('<c>' + cast(@i as varchar) + '</c>' + 
                    '<c>' + cast(@j as varchar) + '</c>' +
                    '<c>' + cast(@k as varchar) + '</c>'
        as xml).query('for $a in /c order by $a return concat($a, "/")').value('.', 'varchar(100)')
    );
    end
    
    alter table dbo.Table1
        add Compare as dbo.fn_GenerateCompare(i, j, k) persisted;
    
    
    select * from dbo.Table1
    

    Возвращается:

    pk  i   j   k   Compare
    --  -   -   -   -------
    1   1   2   3   1/2/3
    2   3   1   2   1/2/3
    3   4   5   6   4/5/6
    4   9   9   9   9/9/9
    

    Теперь ваш запрос должен быть очень простым. Хлопните индексом по новой Compareколонке, и она полетит.


    оригинальный пост:

    Мне нравится идея отсортированного списка, предложенная Торстеном. Вот примерное представление о том, как это можно сделать. Производительность будет значительно улучшена путем сохранения этого compareстолбца в таблице (триггер или сохраненный вычисляемый столбец?

    declare @Table1 table (pk int identity(1,1) primary key, i int, j int, k int)
    declare @Table2 table (pk int identity(1,1) primary key, i int, j int, k int)
    
    insert into @Table1
    values(1, 2, 3), (3, 1, 2), (4, 5, 6), (9,9,9)
    
    
    insert into @Table2
    values (2, 1, 3), (6, 4, 5)
    
    
    --since the order is unimportant, concatenate the columns into a sorted array
    --note how 1,2,3 and 3,1,2 both result in the same compare value:
    select  *
    from    @Table1 o
    cross
    apply   (   select  cast(value as varchar) + '/'
                from    @Table1
                unpivot (value for c in (i,j,k)) as u
                where   pk = o.pk
                order 
                by      value
                for xml path('')
            )d(compare)
    
    --now, bring in the 2nd table
    select  [src] = 1, pk, compare
    from    @Table1 o
    cross
    apply   (   select  cast(value as varchar) + '/'
                from    @Table1
                unpivot (value for c in (i,j,k)) as u
                where   pk = o.pk
                order 
                by      value
                for xml path('')
            )d(compare)
    union all
    select  [src] = 2, pk, compare
    from    @Table2 o
    cross
    apply   (   select  cast(value as varchar) + '/'
                from    @Table2
                unpivot (value for c in (i,j,k)) as u
                where   pk = o.pk
                order 
                by      value
                for xml path('')
            )d(compare)
    
    
    --now just group them to find the matching rows
    select min(src), min(pk), compare
    from    (   
                select  [src] = 1, pk, compare
                from    @Table1 o
                cross
                apply   (   select  cast(value as varchar) + '/'
                            from    @Table1
                            unpivot (value for c in (i,j,k)) as u
                            where   pk = o.pk
                            order 
                            by      value
                            for xml path('')
                        )d(compare)
                union all
                select  [src] = 2, pk, compare
                from    @Table2 o
                cross
                apply   (   select  cast(value as varchar) + '/'
                            from    @Table2
                            unpivot (value for c in (i,j,k)) as u
                            where   pk = o.pk
                            order 
                            by      value
                            for xml path('')
                        )d(compare)
            )grouped
    group
    by      compare
    having  count(*) > 1;