How do I assign group Id's from one table to r

问题:

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.

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

Please and most definitely thank you.

回答1:

I would try someting like that:

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