PostgreSQL子查询问题：根据指定时间关联查询同名用户数据

问题背景 #

我创建了如下student表并插入数据：

create table student (id integer, firstname varchar(100), lastname varchar(100), time varchar(100));
insert into student (id, firstname, lastname, time) values (1, 'albert', 'einstein', 'today');
insert into student (id, firstname, lastname, time) values (2, 'isaac', 'newton', 'today');
insert into student (id, firstname, lastname, time) values (3, 'marie', 'curie', 'today');
insert into student (id, firstname, lastname, time) values (4, 'Aneesh', 'pn', 'yesterday');
insert into student (id, firstname, lastname, time) values (5, 'joe', 'curie', 'yesterday');
insert into student (id, firstname, lastname, time) values (6, 'Aneesh', 'hahha', 'yesterday');
insert into student (id, firstname, lastname, time) values (7, 'Aneesh', 'narayanan', 'today');
select * from student;

需求：输入指定时间，先获取该时间匹配的所有记录的firstname，再返回所有匹配这些firstname的记录。例如输入'yesterday'时，预期返回：

id| firstname | lastname| time
-------------------------------
4 | Aneesh    | pn | yesterday
5 | joe       | curie | yesterday
6 | Aneesh    | hahha | yesterday
7 | Aneesh    | narayanan | today

我尝试了如下查询但存在语法错误，请求正确的PostgreSQL查询语句：

select (
                'Id',id,
                'FirstName',firstname,
                'LastName',lastname,
                'Time',time
          ) as json 
          from
            (
              select firstname from student
                where time= 'yesterday') as tt
             where firstname = tt.firstname;

错误分析 #

你的查询存在两个核心问题：

1. 子查询tt仅返回firstname字段，外层查询未关联原student表，无法直接获取id、lastname等字段。
2. 用括号拼接字符串和字段的方式无法生成合法JSON，PostgreSQL有专门的JSON构造函数用于这类场景。

正确的查询语句 #

方法1：IN子查询（最直观） #

先筛选指定时间对应的firstname集合，再用该集合匹配原表所有记录：

SELECT id, firstname, lastname, time
FROM student
WHERE firstname IN (
    SELECT firstname
    FROM student
    WHERE time = 'yesterday'
);

如果需要返回JSON格式结果，使用json_build_object构造：

SELECT json_build_object(
    'Id', id,
    'FirstName', firstname,
    'LastName', lastname,
    'Time', time
) AS json
FROM student
WHERE firstname IN (
    SELECT firstname
    FROM student
    WHERE time = 'yesterday'
);

方法2：自连接查询（效率更优） #

通过自连接关联原表和筛选后的firstname集合，用DISTINCT减少重复匹配：

SELECT s.id, s.firstname, s.lastname, s.time
FROM student s
JOIN (
    SELECT DISTINCT firstname
    FROM student
    WHERE time = 'yesterday'
) tt ON s.firstname = tt.firstname;

测试结果 #

执行上述任意查询，输入'yesterday'时都会返回预期结果：包含所有Aneesh和joe的记录，无论其time字段是today还是yesterday。

内容的提问来源于stack exchange，提问作者Aneesh Narayanan