postgresql - 如何连接两个查询结果？

Question

我想检索特定时间跨度内的所有 ID。然而，时间戳存储在不同的表中:

Table A has column  my_id
Table B has columns my_id, timestamp

我想要类似的东西

SELECT
  id,
  time
FROM
  (SELECT my_id AS id FROM A) q1,
  (SELECT timestamp AS time FROM B WHERE my_id = id) q2
;

但是如何在不同的子查询中获取 id 的值呢？有没有这个问题的优雅解决方案？

Answer 1

我看到在第二个子查询中您尝试链接两个表:

(SELECT my_id AS id FROM A) q1,
(SELECT timestamp AS time FROM B WHERE my_id = id) q2

如果两个 id 必须相等:

SELECT a.my_id as id, b.timestamp as time
FROM A a
JOIN B b ON (a.my_id = b.my_id);

希望对您有所帮助。