对称交叉连接 [英] Symmetric cross join

问题描述

我正在尝试从表中的每个元素中针对同一个表中的每个元素提取所有对说 i,j，这里是我的查询:

I'm tryng to extract all pair say i,j from each element in a table against each element on the same table, here my query:

select a.Id L,b.id R into #cross from MyTable a cross join mytable b 

我的情况是 i,j == j,i 所以只需要一半的记录.我天真的尝试是:

I'm in the situation where i,j == j,i so just half the record are needed. My naive attempt is:

select a.Id L,b.id R into #cross from MyTable a cross join mytable b 
where not exists
    (select * from #cross c where c.L=R and c.R=L)

但是我在插入时无法查询目标表，正如 SQL Server 所说:

but I can't query the destination table while inserting in, as said by SQL Server:

The SELECT INTO statement cannot have same source and destination tables

我该怎么做才能有效?

编辑仅供参考，我说我需要一半的记录"，这是错误的，考虑到i,j == j,i后的记录数是n*(n+1)/2

EDIT Just for reference, I said "I need half the records", that is wrong, the record count after taking in account that i,j == j,i is n*(n+1)/2

推荐答案

所以，只要条件连接，使左边总是等于或小于！

So, just condition the join so that the left side is always equal to or lesser!

    select a.Id L,b.id R
      into #cross
      from MyTable a
inner join mytable b on a.id <= b.id

这篇关于对称交叉连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！