sql - SQL查询基于最近的时间戳联接两个表

Question

我在SQL中有两个表，我需要能够根据表B中的时间戳早于或等于表A中的时间戳进行联接。

因此，这是两个表和所需输出的一些虚假数据：

结案案例（表A）

| id | resolution |         timestamp          |------------------------------------------------|  1 |     solved | 2006-10-05 11:55:44.888153 ||  2 |     closed | 2007-10-07 12:34:17.033498 ||  3 |    trashed | 2008-10-09 08:19:36.983747 ||  4 |     solved | 2010-10-13 04:28:14.348753 |

Classification (Table B)

| id |    value    |         timestamp          |-------------------------------------------------|  1 |    freshman | 2006-01-01 12:02:44.888153 ||  2 |   sophomore | 2007-01-01 12:01:19.984333 ||  3 |      junior | 2008-01-01 12:02:28.746149 |

Desired Results

| id | resolution |         timestamp          |    value    |--------------------------------------------------------------|  1 |     solved | 2006-10-05 11:55:44.888153 |    freshman ||  2 |     closed | 2007-10-07 12:34:17.033498 |   sophomore ||  3 |    trashed | 2008-10-09 08:19:36.983747 |      junior ||  4 |     solved | 2010-10-13 04:28:14.348753 |      junior |

So, I know the code needs to look like the following, I just can't figure out what to do with the ON portion of the JOIN ($1 and $2 are variables that will be passed in):

SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class ON ???
  WHERE case.timestamp BETWEEN $1 AND $2;

我知道我可以使用子选择，但是它将至少在几千行上运行，可能还会更多，而且我需要它真的要快。所以我希望有一个简单的子句可以做到。

Answer 1

如果可以更改表结构，建议更改分类表以包括结束日期和开始日期-这样一来加入表将更加容易。

如果没有，我建议如下：

SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN (select c.*, 
                    (select min(timestamp)
                     from classifications c1
                      where c1.timestamp > c.timestamp) timeend
             from classifications c) AS class 
  ON case.timestamp >= class.timestamp and 
     (case.timestamp < class.timeend or class.timeend IS NULL)
  WHERE case.timestamp BETWEEN $1 AND $2;

编辑-结束日期为分类：

SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class 
  ON case.timestamp >= class.timestamp and case.timestamp < class.timeend
  WHERE case.timestamp BETWEEN $1 AND $2;