连接表上的 where 子句

对于下表：

ROOM

+----+--------+
| ID |  NAME  |
+----+--------+
|  1 | ROOM_1 |
|  2 | ROOM_2 |
+----+--------+

房间状态

+----+---------+------+------------------------+
| ID | ROOM_ID | OPEN |          DATE          |
+----+---------+------+------------------------+
|  1 |       1 |    1 |    2000-01-01 00:00:00 |
|  2 |       2 |    1 |    2000-01-01 00:00:00 |
|  3 |       2 |    0 |    2000-01-06 00:00:00 |
+----+---------+------+------------------------+

存储的数据是最后更改状态的空间：

• ROOM_1 于 2000 年 1 月 1 日 00:00:00 开放
• ROOM_2 于 2000 年 1 月 1 日 00:00:00 开放
• ROOM_2 于 2000 年 1 月 6 日 00:00:00 关闭

ROOM_1 仍然开放，ROOM_2 已关闭（自 2000 年 1 月 6 日起未开放）。如何通过连接选择实际打开的房间名称？如果我写：

SELECT ROOM.NAME 
FROM ROOM
  INNER JOIN ROOM_STATE ON ROOM.ID = ROOM_STATE.ROOM_ID
WHERE ROOM_STATE.OPEN = 1

选择 ROOM_1 和 ROOM_2 是因为ROOM_STATE with ID 2 is OPEN.

SQL 小提琴：http://sqlfiddle.com/#!9/68e8bf/3/0

在 Postgres 中，我会推荐distinct on:

select distinct on (rs.room_id) r.name, rs.*
from room_state rs join
     room r
     on rs.room_id = r.id
order by rs.room_id, rs.date desc;

distinct on特定于 Postgres。它保证每个房间的结果只有一行（这就是您想要的）。所选行是遇到的第一行，因此选择日期最大的行。

另一个有趣的方法是使用横向连接：

select r.*, rs.*
from room r left join lateral
     (select rs.*
      from room_state rs
      where rs.room_id = r.id
      order by rs.date desc
      fetch first 1 row only
     ) rs;