SQL中与第二个字段相交的返回字段

Question

我试图返回具有特定人员相交字段的字段。我的意思是

Name    Friend
----    -----
Joe     Sally
Joe     Bill
Mary    Sally
Mary    Michael
Mike    Joe
Bill    Bill
Bill    Sally
Gil     Sally
Gil     Bill
Gil     David

比如说，我们想要匹配Joe的第二列的人的列表，他们必须同时匹配Sally和Bill。所以只有比尔符合这个标准，因为玛丽有一个，但她没有比尔。吉尔有莎莉和比尔，但他也有大卫。所以只有比尔应该被归还。我在想INTERSECT的东西会起作用，因为它返回公共字段，但这不能说明有人有更多，我认为。不知道如何写一个SQL查询做我想要的。

很明显，与Joe有相同朋友的名字列表

Answer 1

无重复

SELECT p2.name
FROM   people AS p1
JOIN   people AS p2 ON p2.number = p1.number
                   AND p2.name <> p1.name    -- exclude self-join
WHERE  p1.name = 'Joe'
AND NOT EXISTS (
   SELECT 1 
   FROM   people p3
   WHERE  p3.name = p2.name  
   AND    p3.number <> p1.number
   )
GROUP  BY p2.name
HAVING count(*) = (SELECT count(*) FROM people WHERE name = 'Joe')

最后一个条件AND NOT EXISTS ...仅在您想要排除具有额外好友Joe没有的人时才需要。
它从结果中排除了您示例中的Gil。

这是关系除法的特殊情况(带有自引用表)。您可以在以下相关答案中找到完整的查询技术库:
如何过滤多通关系中的SQL结果

与

副本

如果可以有重复(比如你的问题初稿)，事情就变得有点复杂了:

WITH p AS (
   SELECT name, number, count(*) AS ct
   FROM   people
   GROUP  BY name, number
   )
SELECT p2.name
FROM   p AS p1
JOIN   p AS p2 ON p2.number = p1.number
              AND p2.ct = p1.ct
              AND p2.name <> p1.name    -- exclude self-join
WHERE  p1.name = 'Joe'
AND NOT EXISTS (
   SELECT 1 
   FROM   p p3
   WHERE  p3.name = p2.name  
   AND    p3.number <> p1.number
   )
GROUP  BY p2.name
HAVING count(*) = (SELECT count(*) FROM p WHERE name = 'Joe')