基于最接近时间戳连接两个表的 SQL 查询

发布于 2024-09-30 07:12:05 字数 1521 浏览 5 评论 0原文

我在 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 |

分类（表 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 |

期望结果

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

所以，我知道代码需要如下所示，我只是不知道如何处理 JOIN 的 ON 部分（$1 和 $2 是将传入的变量）：

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;

我知道我可以使用 sub -select，但这将在至少几千行上运行，可能更多，而且我需要它非常快；所以我希望有一个简单的子句可以做到这一点。

原文

I have two tables in SQL and I need to be able to do a join based off of the timestamp in table B that is earlier than or equal to the timestamp in table A.

So, here is some fake data for two tables and the desired output:

Closed Cases (Table 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;

I know I could use a sub-select, but this will be operating on at least a few thousand rows, probably more, and I need it to be really fast; so I was hoping for a simple clause that could do it.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

半暖夏伤 2024-10-07 07:12:05

如果您可以更改表结构，我建议更改分类表以包含结束日期和开始日期 - 以这种方式连接到表会更容易。

如果没有，我建议如下：

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;

If you can make changes to the table structures, I recommend changing the classification table to include an end date as well as a start date - it will be much easier to join to the table that way.

If not, I suggest the following:

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;

EDIT - with the end date on classification:

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;

回复收藏 0 原文

不再让梦枯萎 2024-10-07 07:12:05

如果您的 SQL 有窗口函数，那么可能是版本 8 及更高版本，最简单的方法是使用分类时间戳上的LEAD函数，有效实现了更简单的@user359040的解决方案

回复收藏 0 原文

风追烟花雨 2024-10-07 07:12:05

更改时间戳并使用 int 作为连接表的键。更快

这会比比较日期表 1
字段1
字段2
字段3
连接器字段

表2
字段1
字段2
字段3
ConnectorField

和您需要做的就是
select * from table1 T1 内联 table2 T2 on T1.ConnectorField = T2.ConnectorField

回复收藏 0 原文

溺深海 2024-10-07 07:12:05

SELECT case.id, case.resolution, case.timestamp, class.value
  FROM closed_cases AS case
  LEFT JOIN classifications AS class 
  ON case.timestamp >= class.timestamp
  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
  WHERE case.timestamp BETWEEN $1 AND $2;

回复收藏 0 原文

~没有更多了~