使用 SQLite 从两列中获取唯一的对称对 [英] Get unique symmetric pairs from two columns with SQLite

问题描述

我一直在寻找 Stack 上的类似问题，但其中任何一个似乎对我都有帮助.我有一个名为喜欢"的表格:

I have been looking at similar questions on Stack but any of them seems to be helpful to me. I have the following table called "Likes":

 ID1    ID2
1689    1709
1709    1689
1782    1709
1911    1247
1247    1468
1641    1468
1316    1304
1501    1934
1934    1501
1025    1101

我想获取 ID1 和 ID2 两列之间的唯一对.即:

I want to get the unique pairs between the two columns ID1 and ID2. That is:

 ID1    ID2
1689    1709
1501    1934

有什么想法吗?谢谢.

推荐答案

看起来您想要两个版本中都存在的对，即 (x, y) 和 (y, x).

It looks like you want pairs that exist in both versions, i.e. both as (x, y) and (y, x).

可以使用 EXISTS 查询来完成:

Can be done with a EXISTS query:

select t1.c1, t1.c2
from tablename t1
where t1.c1 < t1.c2
  and exists (select 1 from tablename t2
              where t1.c1 = t2.c2
                and t1.c2 = t2.c1)

或者作为JOIN:

select t1.c1, t1.c2
from tablename t1
join tablename t2
    on  t1.c1 = t2.c2
    and t1.c2 = t2.c1
where t1.c1 < t1.c2

这篇关于使用 SQLite 从两列中获取唯一的对称对的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！