SQlite:如何找到互斥对? [英] SQlite: how to find mutually exclusive pairs?

问题描述

有一个表 Likes 与字段 ID1 ID2 包含互斥对像

There is a table Likes with fields ID1 ID2 which contain mutually exclusive pairs like

1689    1709

和

1709    1689 

我想找到他们.我用串联的方式试过了，没用.

I want to find them. I tried it by concatenation and it didn't work.

select L.ID1,L.ID2
from Likes L
where (L.ID1||L.ID2) = (L.ID2||L.ID1);

推荐答案

您必须将表格加入到自身中:

You’ll have to join the table to itself:

SELECT L1.ID1, L1.ID2 FROM sometable L1
JOIN sometable L2 ON L1.ID1=L2.ID2 AND L1.ID2=L2.ID1

这将显示表中具有相反对的 ID 对.这也会以其他方式显示该对(1,2 以及 2,1)，如果您不想要，您可以添加 WHERE L1.ID1 例如.

This will show you the ID pairs that have the opposite pair in the table. This will also show the pair other way around also (1,2 as well as 2,1), if you don’t want that you can add WHERE L1.ID1<L1.ID2 for example.

您也可以使用 EXISTS 子句，但我认为这更简单.

You could also use EXISTS clause but I think this is simpler.

这篇关于SQlite:如何找到互斥对?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！