以任意顺序比较 2 个或更多列值

Question

我需要一种快速的方法来比较来自不同表的 2 个或更多值，其中订单任意存储在 sql 服务器中。 数据来自不会更改的第三方。

下面的示例数据显示了以两种方式描述的同一项目。 其余列包含我正在加入的其他数据。

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

有没有办法在进行比较之前对前两列返回的数据进行排序，这样我就不必创建所有联合？

Answer 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列上拍打索引，它应该会飞起来。

---

原文：

我喜欢Thorsten提出的排序列表想法。 以下是如何完成的粗略想法。 将此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;