联接表上的where子句 [英] Where clause on joined table

问题描述

对于以下表格：

房间

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

ROOM_STATE

+----+---------+------+------------------------+
| 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 |
+----+---------+------+------------------------+

存储的数据是上次更改状态的空间：

Stored data is room with last changed state:

• ROOM_1于2000-01-01 00:00:00打开


• ROOM_2于2000-01-01 00:00:00


• ROOM_2于2000-01-06 00:00:00

ROOM_1仍处于打开状态，ROOM_2已关闭（自2000年1月6日以来未打开）。如何通过联接选择实际打开的房间的名称？如果我写了：

ROOM_1 is still open, ROOM_2 is closed (no opened since 2000-01-06). How to select actual opened rooms names with a join ? If i wrote:

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是因为<$具有 ID 2 的c $ c> ROOM_STATE 是 OPEN 。

ROOM_1 and ROOM_2 are selected because ROOM_STATE with ID 2 is OPEN.

SQL Fiddle： http://sqlfiddle.com/#!9/68e8bf/3/0

SQL Fiddle: http://sqlfiddle.com/#!9/68e8bf/3/0

推荐答案

在Postgres中，我建议在上区别：

In Postgres, I would recommend 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;

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

distinct on is specific to Postgres. It guarantees that the results have only one row for each room (which is what you want). The chosen row is the first row encountered, so this chooses the row with the largest date.

另一种有趣的方法是使用横向联接：

Another fun method is to use a lateral join:

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;

这篇关于联接表上的where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！