选择范围内的时间戳 [英] select timestamps within ranges

问题描述

我有两张桌子.示例表 1:

I have got 2 tables. Example Table 1:

    ID        episode_id     episode_start      episode_end
    ----------------------------------------------------
    1             1            1                  2
    1             2            4                  5
    1             3            96                 105
    1             4            110                114 
    2             1            1                  4
    2             2            13                 24

示例表 2

    ID        timestamp       Other_info   
    --------------------------------
    1             1            111                  
    1             2            142                
    1             3            114                
    1             4            112                
    1             5            116                
    1             6            123                 
    2             1            145                 
    2             2            156                
    2             3            154                

我想根据 table2.timestamp = 在 table1.episode_start 和 table1.episode_end 之间合并两个表.

I would like to merge the two tables based upon table2.timestamp = between table1.episode_start and table1.episode_end.

最终表应该是表 2 的一个子集，只有发生剧集的时间戳行.

The final table should be a subset of table 2 with only the timestamprows where there is an episode.

问题:如何做到这一点?计算效率最高的方法是什么?

实际上我的桌子要长得多.

In reallity my tables are much longer.

因此，例如，ID = 1 的 episode_start 与第 12 集中 ID=1200 的剧集开始相同.因此，简单地将它们合并是行不通的.

So, for example, the episode_start of ID = 1 be the same as the episode start of ID=1200 in episode 12. So simply merging them does not work.

预期输出:

    ID        timestamp       Other_info   
    --------------------------------
    1             1            111                  
    1             2            142                            
    1             4            112                
    1             5            116                              
    2             1            145                 
    2             2            156                
    2             3            154   

等等.合并基本上是表 2 和表 1 的某种过滤器.

and so on. The merge is basically some sort of filter of table 2 by table 1.

推荐答案

select table2.id, timestamp, other_info
from table1
left join table2 on table2.timestamp between table1.episode_start and table1.episode_end
where table2.id = table1.id

输出:

id         timestamp      Other_info
---------------------------------------
1              1              111
1              2              142
1              4              112
1              5              116
2              1              145
2              2              156
2              3              154

这篇关于选择范围内的时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！