如何根据起始时间和结束日期时间之间的日期时间将一个表中的组ID分配给另一表中的行? [英] How do I assign group Id's from one table to rows in another based on datetimes being between start and finish datetimes?

问题描述

我有两个桌子.表A有两列，一列是日期时间，一列是数字.表B有几列，但此问题有三列:每行的唯一ID，开始日期时间和结束日期时间.表A对于表B的每一行都有多行，因此我想根据表A的日期时间是否发生在表B的开始日期时间和结束日期时间之间，将表B的行ID分配给表A的相应行.假设表B中没有行具有重叠的开始-结束间隔.

I have two tables. Table A has two columns, one datetime, one numeric. Table B has several columns, but three essential columns to this problem: a unique Id for each row, a start datetime and an end datetime. Table A has multiple rows for each single row of Table B, so I want to assign the row Id's from Table B to the corresponding rows of Table A based on whether the Table A datetime occurs BETWEEN the start datetime and end datetime of Table B. Assume no rows in Table B have overlapping start-end intervals.

想像一下，基本上有许多实验是在开始和结束时间进行的，但是该实验的测量值是单独记录的，没有实验编号，并且只有日期时间作为参考，在此期间进行观察.

Imagine essentially many experiments were done with start and end times, but the measurements for the experiment were recorded separately without the experiment id's and only with datetimes to reference during which experiment the observations were made.

我如何获得分配给表A中的观察值的表B ID?

HOW DO I GET THE TABLE B ID'S ASSIGNED TO THE OBSERVATIONS IN TABLE A?

请，绝对要谢谢你.

推荐答案

我会这样尝试:

select a.*, b.id
from a, b
where a.dt between b.start_dt and b.end_dt;

这篇关于如何根据起始时间和结束日期时间之间的日期时间将一个表中的组ID分配给另一表中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！