从在另一列中共享值的一列中选择值

Question

这就是问题所在。该数据库描述了物体(不同物种的基因)之间的对称和可转移关系。如果物种1的X基因与物种2的Y基因相关，而物种2的Y基因与物种3的Z基因相关，则物种1的X基因与物种3的Z基因相关。

下面是一个示例表：

species1 gene1 species2 gene2
2        Y     1        X
2        Y     3        Z

现在，这是我想要做的。给定物种1和3(都在species2栏中)，在gene2列中找到gene2中的所有基因，其中gene1列中有一个共同的值。

基本上，这就是我想要的输出：

X  Z

..。对于满足这个条件的每一对。

请注意，每个基因id只存在于一个物种中。

说明:列species1包含少数“中心”物种(如人类)。因此，如果我想找到一种与老鼠基因Card9相匹配的老鼠基因，有两种可能：(一)人类基因组中没有匹配，在这种情况下，gene1列中将有老鼠基因匹配，或者老鼠基因将在gene1中，老鼠基因将在gene2列中；(二)人类(或其他一些)基因组中有匹配，在这种情况下，我首先需要在gene1列中找到人类基因组中的匹配，然后在gene2列中找到与该基因匹配的基因。

显然，我想我可以在SQL之外完成这个任务：

1. 从物种1中选择gene2列中的所有基因
2. 从物种3中选择gene2列中的所有基因
3. 对于物种1的每个基因，在gene1列中找到匹配的基因，其中species2列为3。

但是，我确信有一个聪明的SQL语句可以做到这一点。

这个数据库有大约五百万行。

Answer 1

您可以通过自连接来完成这一任务：

select t1.gene2 result1, t2.gene2 result2 
from tablename t1 inner join tablename t2
on t2.gene1 = t1.gene1
where t1.species2 = 1 and t2.species2 = 3

见演示。

进行聚合的另一种方法是：

select min(gene2) result1, max(gene2) result2
from tablename
where species2 in (1, 3)
group by gene1
having result1 <> result2;

见演示。

结果：

| result1 | result2 |
| ------- | ------- |
| X       | Z       |

Answer 2

如果我正确理解，这是一个自我连接：

select distinct g1.gene2
from genes g1 join
     genes g3
     on g1.species2 = 1 and
        g3.species2 = 3 and
        g1.gene2 = g3.gene2 and
        g1.gene1 = g3.gene1;