postgreSQL查询，以返回基于条件的特定匹配值的表

Question

我在PostgreSQL数据库中有3个表：

person (id, first_name, last_name, age)
interest (id, title, person_id REFERENCES person)
location (id, city, state text NOT NULL, country, person_id REFERENCES person)

city可以为空，但state和country不能为空。一个人可以有很多兴趣，但只能在一个地方。我的挑战是返回一张有着相同兴趣和地点的人的桌子。

所有ID都是序列化的，因此会自动创建。假设我有4个人住在"；TX"；，他们每个人都有两个兴趣，但只有人1和3有相似的兴趣，比如说"；枪；(毕竟是得克萨斯州)。我需要从人员表中选择所有人员，其中人员的兴趣头衔(因为id是自动生成的，两支枪的兴趣将导致两个不同的id密钥)与另一个人的兴趣头衔相等，并且城市或州也相等。

我在这里看到了这个问题的答案，从SQL Server中选择具有匹配列的行，我觉得逻辑与我的问题有点相似，不同的是他有两个表，在我有三个表的地方连接在一起。

Answer 1

返回一个具有相同兴趣和位置的人员表。

我将把它解释为"表CCD_ 4中存在共享CCD_ 5中至少一个匹配行和位置上的匹配行的另一行的所有行。没有特别的订单">

在子查询中使用窗口函数的简单解决方案：

SELECT p.*
FROM  (
SELECT person_id AS id, i.title, l.city, l.state, l.country
, count(*) OVER (PARTITION BY i.title, l.city, l.state, l.country) AS ct
FROM   interest i
JOIN   location l USING (person_id)
) x
JOIN   person p USING (id)
WHERE  x.ct > 1;

这将NULL值视为"0"；相等"；。(您没有明确说明。)

根据未公开的基数，可能会有更快的查询样式。(比如先减少重复的兴趣和/或地点。)

亚洲1:

列birthday(或year_of_birth)几乎总是比列age好，后者会立即开始位腐烂。

亚洲2:

一个人只能有[…]一个位置。

您至少可以在location.person_id上添加一个UNIQUE约束来强制执行该约束。(如果您不能将其作为PK或仅将位置列附加到person表中。)