将列组合到WHERE IN()的记录中

Question

首先，我很抱歉。我无法控制数据的存储方式，所以我必须按原样使用它。

我在一个普适数据库中有一些表，看起来像这样：

Records:
id |  first_name  |  last_name  | address   | phone ....
1 |  John
2 |  Bill
3 |  Jared
4 |  Dave

Users:
id | parent_id| first_name
2 |        2 | Bill
3 |        1 | Jared
4 |        1 | Dave
Times:
user_id | Date
4      | 2020-09-29

这些是医疗记录，User将是记录的患者，可能是3岁，而Records将包含账单信息，但他们可能是也可能不是患者。

我需要基于Times表的Records表中的数据，但我必须通过Users表才能获得关联。问题是，我想要的不仅仅是获得完全匹配的记录，而是将它们分配给family组，并为该family的所有成员返回Records

因此，在这种情况下，我将查看Times表，得到与Users表匹配的user_id4，得到family1中的Dave。我现在需要在family中找到所有的Users，然后从Records表中获取所有这些记录，以及John的记录。

我以前的计划是将两列id和parent_id变平，只返回在特定时间范围内的列。到目前为止，这是我唯一能弄清楚的方法：

SELECT "parent_id" as ids from  "Users" as u
where u."parent_id" IN(
SELECT  "parent_id" FROM "Users" where "id" IN(  
SELECT "user_id"
FROM Times as a
WHERE a.Date >=  CURRENT_DATE() - 2
AND a.Date <=  CURRENT_DATE() + 5
)
)
UNION ALL
SELECT "id" as ids from  "Users" as u
where u."parent_id" IN(
SELECT  "parent_id" FROM "Users" where "id" IN(  
SELECT "user_id"
FROM Times as a
WHERE a.Date >=  CURRENT_DATE() - 2
AND a.Date <=  CURRENT_DATE() + 5
)
)

问题是，我需要将它用作另一个查询的过滤器，如下所示：

SELECT id, first_name, last_name 
FROM Records WHERE id 
IN(
insert query here
)

哪个会返回：

Output:
id |  first_name  |  last_name  
1 |  John        |  Smith
3 |  Jared       |  Smith
4 |  Dave        |  Smith

我在这里能做什么？效率很重要，这些表有几十万条记录。由于UNION ALL，我无法使用我在Pervasive中拥有的内容。

Answer 1

我创建了您的表，并根据您的帖子添加了一些数据，并在发布时尝试了您的查询，结果成功了。我没有足够的数据来测试性能，但您应该能够使用查询计划/查询计划查看器来确定语句是否已优化。我同时使用了PSQL11和Zen14(当前版本的PervasivePSQL(，得到了相同的(预期的(结果。我使用的查询是：

create table "Users" 
(id int,
parent_id int, 
first_name char(100)
);
create table times 
(user_id int, 
"Date" date);
create table records
(id int,
first_name char(100), 
last_name char(100), 
address char(250), 
phone char(13));
insert into records values (1, 'john', 'smith', 'addr1', 'phone');
insert into records values (2, 'Bill', 'smith', 'addr1', 'phone');
insert into records values (3, 'Jared', 'smith', 'addr1', 'phone');
insert into records values (4, 'Dave', 'smith', 'addr1', 'phone');
insert into "Users" values (2,2,'Bill');
insert into "Users" values (3,1,'Jared');
insert into "Users" values (4,1,'Dave');
insert into Times values (4,'2020-09-29');
SELECT id, first_name, last_name 
FROM Records WHERE id 
IN(
SELECT "parent_id" as ids from  "Users" as u
where u."parent_id" IN(
SELECT  "parent_id" FROM "Users" where "id" IN(  
SELECT "user_id"
FROM Times as a
WHERE a.Date >=  CURRENT_DATE() - 2
AND a.Date <=  CURRENT_DATE() + 5
)
)
UNION ALL
SELECT "id" as ids from  "Users" as u
where u."parent_id" IN(
SELECT  "parent_id" FROM "Users" where "id" IN(  
SELECT "user_id"
FROM Times as a
WHERE a.Date >=  CURRENT_DATE() - 2
AND a.Date <=  CURRENT_DATE() + 5
)
)
)