在不知道第二个cols值的情况下，在不同列中查找共享相同值的列中的值

Given This Table:

鉴于这种表:

Relationship

managerId  companyId
   12         33
   19         33
   27         44
   21         33
    4         20

Is there a way to find all managerId's that share the same companyId but only by knowing ONE of the managerId's and not knowing the companyId

是否有一种方法可以找到所有的managerId共享相同的公司id，但只需要知道其中一个managerId而不知道公司id

So for example, if we only know that the managerId is 12

例如，如果我们只知道managerId是12

SELECT companyId
FROM Relationship
WHERE managerId = 12

We will obviously get 33 back. But within the same query is there a way to get back all managerId's where the companyId is the value of the return from that first statement. So in this case just by knowing managerId=12 I want to get back 12,19,21.

很明显我们会得到33。但是在同一个查询中有一种方法可以返回所有managerId，其中companyId是第一个语句返回的值。在这种情况下，通过知道managerId=12，我想要返回12 19 21。

3 个解决方案

#1

Join the table to itself on companyId:

在公司id上加入表格:

select b.managerId
from relationship a
join relationship b on b.companyId = a.companyId
where a.managerId = 19

#2

Here is one method:

这里有一个方法:

select r.*
from relationship r
where r.companyid = (select r2.companyid from relationship r2 where r2.managerid = 12);

Note: This particular method assumes that relationship.managerid is unique. This seems reasonable given your problem and sample data.

注意:这个特殊的方法假定了这种关系。managerid是独一无二的。考虑到您的问题和示例数据，这似乎是合理的。

#3

try this:

试试这个:

select managerId from relationship
where companyId = select companyId from relationship where managerId = 12;

#1