SQL简单查询

Question

我有下表Persons_Companies，它显示了这些人所知道的个人和公司之间的关系:

PersonID  |  CompanyID

   1             1
   2             1
   2             2
   3             2
   4             2

假设公司 1 =“Google”，公司 2 =“Microsoft”，我想知道查询会得到以下结果:

PersonID  |  Microsoft    |    Google

   1             0                1
   2             1                1
   3             1                0
   4             1                0

直到此刻我有类似的东西:

select PersonID,
case when CompanyID=1 then 1 else 0
end as Google,
case when EmpresaID=2 then 1 else 0
end as Microsoft
from Persons_Companies

我的问题是了解这两家公司的人，我无法想象这个查询怎么可能。

什么是 SQL 查询？

Answer 1

select PersonID,
case when EXISTS (
   SELECT 1 
   FROM Persons_Companies pc1 
   WHERE pc.PersonID = pc1.PersonID and pc1.CompanyID = 1 ) then 1 else 0
end as Google,
case when EXISTS (
   SELECT 1 
   FROM Persons_Companies pc2
   WHERE pc.PersonID = pc2.PersonID and pc2.CompanyID = 2 ) then 1 else 0
end as Microsoft
from Persons_Companies pc