没有重复组合的交叉连接 [英] Cross Join without duplicate combinations
本文介绍了没有重复组合的交叉连接的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我知道这个问题与这个问题非常相似:对称交叉连接还有这个:sql 中交叉连接的组合(不是排列)>
但是如果我们有两个不同的表,比如说 A 和 B:
select A.id,B.id from A cross join B
我想考虑 (a,b)
等于 (b,a)
?
解决方案
select A.id aid,B.id bid从 A 内连接 B 上 a.id <= b.id联盟选择 B.id,A.id从 A 内部连接 B 上 b.id <援助
如果你想变得更老练:
选择不同的当 a.id<=b.id 然后 a.id else b.id end id1,当 a.id<=b.id then b.id else a.id end id2从 A 交叉连接 B
在我用小桌子进行的不科学的小烘焙中,后者更快.以及下面,写为子查询的 case
表达式.
选择不同的(select MIN(id) from (select a.id union select b.id)[ ]) id1,(select MAX(id) from (select a.id union select b.id)[ ]) id2从 A 交叉连接 B
I know this question is very similar to this one: Symmetric cross join and this one too: combinations (not permutations) from cross join in sql
But what about if we have two different tables, say A and B:
select A.id,B.id from A cross join B
and I want to consider the pair (a,b)
equal to (b,a)
?
解决方案
select A.id aid,B.id bid
from A inner join B on a.id <= b.id
union
select B.id,A.id
from A inner join B on b.id < a.id
If you wanted to be more sophisticated:
select distinct
case when a.id<=b.id then a.id else b.id end id1,
case when a.id<=b.id then b.id else a.id end id2
from A cross join B
In my little unscientific bake off with tiny tables, the latter was faster. And below, the case
expressions written as subqueries.
select distinct
(select MIN(id) from (select a.id union select b.id)[ ]) id1,
(select MAX(id) from (select a.id union select b.id)[ ]) id2
from A cross join B
这篇关于没有重复组合的交叉连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文