MySQL从具有相同亲属关系的关系表中进行选择

一种方法是使用条件反射 Group_Concat() 具有 Having 条款我们可以得到所有的水果 jack

现在,我们可以为其他人得到同样的水果订单,并使用 HAVING 条款考虑 name 与“jack_水果”匹配的值。

SELECT t1.name AS the_guy 
FROM eaters AS t1 
JOIN (SELECT GROUP_CONCAT(fruit ORDER BY fruit) AS jack_fruits
      FROM eaters 
      WHERE name = 'jack') AS t2 
WHERE t1.name <> 'jack' 
GROUP BY t1.name 
HAVING GROUP_CONCAT(t1.fruit ORDER BY fruit) = MAX(t2.jack_fruits );

结果

| the_guy |
| ------- |
| richard |

View on DB Fiddle

你可以写:

SELECT e2.name
FROM   eaters e1
      INNER JOIN eaters e2
              ON e1.fruit = e2.fruit
WHERE  e1.name = 'Jack'
      AND e2.name != 'Jack'
GROUP  BY e2.name 

SELECT DISTINCT name FROM eaters 
WHERE
    name <> 'Jack' 
    AND 
    fruit IN (SELECT fruit FROM eaters WHERE name = 'Jack')

最好使用这个:

SELECT DISTINCT
       e.name
FROM eaters e
WHERE name <> 'jack'
      AND NOT EXISTS
(
    SELECT DISTINCT
           name
    FROM eaters e1
    WHERE NOT EXISTS
    (
        SELECT * FROM eaters e2 WHERE e2.name = 'jack' AND e1.fruit = e2.fruit
    )
          AND e1.name = e.name
);