sql - 在 sqlite3 中查找关系

Question

我有这张表，上面有父亲和孩子的名字：

ID	名字
1	坦率
2	加布里埃尔
3	杰弗里
4	乔治
5	戈登
6	希瑟

第二个表告诉每个父母的孩子是谁，例如 Geoffrey 和 Gabriel 是 Frank 的孩子，Geoffrey、Gordon 和 Heather 是 Geoffrey 的孩子：

ID	id_parente	id_child
1	1	2
2	1	3
3	3	4
4	3	5
5	3	6

我已经尝试过各种方式来找到一种方法来查询给我父亲的名字给我一个或多个孩子的名字，例如，如果他们给我 George 和 Gordon，那么查询应该给我 Geoffrey；当然，如果他们要求我获取 Gabriel 和 Gordon 的父亲，则查询的结果必须为 Null，因为这两个孩子（Gabriel 和 Gordon）没有同一个父亲。有没有办法在 sqlite3 中进行此查询？提前致谢

标签： sqlsqlite

Answer 1

创建一个 CTE，它返回您希望为其父的人员的姓名。
将表的2 个副本加入persons到表中以聚合并设置子句relations中所有名称具有共同父亲的条件：HAVING

WITH cte(names) AS (VALUES ('George'), ('Gordon')) -- change to the name you want
SELECT p1.names
FROM persons p1
INNER JOIN relations r ON r.id_parente = p1.id
INNER JOIN persons p2 ON p2.id = r.id_child
WHERE p2.names IN cte
GROUP BY p1.id
HAVING COUNT(*) = (SELECT COUNT(*) FROM cte)

或者，没有 CTE：

SELECT p1.names
FROM persons p1
INNER JOIN relations r ON r.id_parente = p1.id
INNER JOIN persons p2 ON p2.id = r.id_child
WHERE p2.names IN ('George', 'Gordon')
GROUP BY p1.id
HAVING COUNT(*) = 2 -- the number of persons in the IN list

请参阅演示。