给出下表:
CREATE TABLE people (
name TEXT PRIMARY KEY,
age INT NOT NULL
);
INSERT INTO people VALUES
('Lisa', 30),
('Marta', 27),
('John', 32),
('Sam', 41),
('Alex', 12),
('Aristides',43),
('Cindi', 1)
;
我使用自连接将特定列的each value与同一列的all the other values进行比较.我的查询如下所示:
SELECT DISTINCT A.name as child
FROM people A, people B
WHERE A.age + 16 < B.age;
该查询旨在根据年龄差异找出潜在的儿子/女儿.更具体地说，我的目标是确定可能与父母之一住在同一所房子里的人(按姓名排序)，假设子元素和父母之间的年龄差异至少为16岁.
现在我想把这种逻辑与另一个表中的信息结合起来.
另一张桌子看起来像这样:
CREATE TABLE houses (
house_name TEXT NOT NULL,
house_member TEXT NOT NULL REFERENCES people(name)
);
INSERT INTO houses VALUES
('house Smith', 'Lisa'),
('house Smith', 'Marta'),
('house Smith', 'John'),
('house Doe', 'Lisa'),
('house Doe', 'Marta'),
('house Doe', 'Alex'),
('house Doe', 'Sam'),
('house McKenny', 'Aristides'),
('house McKenny', 'John'),
('house McKenny', 'Cindi')
;
这两个表可以连接ON houses.house_member = people.name.
更具体地说，我想指出子元素们只在同一所房子里.将每个人的年龄与所有其他人的年龄进行比较是没有意义的，而是it would be more efficient to compare the age of each person with all the other people in the same house.岁
我的 idea 是从上面执行自连接，但只在PARTITION BY household_name以内.然而，我认为这不是一个好主意，因为我没有聚合函数.GROUP BY条语句也是如此.我能在这里做什么？
预期输出应如下，按house_member排序:
| house_member |
|---|
| Alex |
| Cindi |
为了简单起见，我创建了一个fiddle.