我有一个表注册
registration table
+------+------+
|ARIDNR|LIEFNR|
+------+------+
|1 |A |
+------+------+
|2 |B |
+------+------+
|3 |C |
+------+------+
UserLike Table
+------+------+
|ARIDNR|LIEFNR|
+------+------+
|A |B |
+------+------+
|B |A |
+------+------+
|A |C |
+------+------+
我想选择具有类似用户的表的联接查询,当两个用户都喜欢对方时,这些值返回true
示例
用户A和用户B一样用户B就像用户A
所以我在用户B的响应中得到了返回true
在这种情况下,用户A与用户C相似,但用户C与用户A不同在这种情况中,返回错误的
我想要像下面这样的输出
结果
{
“username”:B
“match”:true
},
{
“username”:C
“match”:false
}
假设现在重复,您可以执行:
select least(ARIDNR, LIEFNR), greatest(ARIDNR, LIEFNR)
from userlike
group by least(ARIDNR, LIEFNR), greatest(ARIDNR, LIEFNR)
having count(*) = 2;
或者,更有效地(使用正确的索引(为:
select ul.*
from userlike ul
where ul.ARIDNR < ul.LIEFNR and
exists (select 1
from userlike ul2
where ul2.ARIDNR = ul.LIEFNR and
ul2.LIEFNR = ul.ARIDNR
);