SQL:使用来自其他查询的两个日期来选择日期间隔之间的数据

I am new to SQL and I am facing a complicated problem.

我是SQL新手，我面临着一个复杂的问题。

My table T2 contains start_date and end_date, two time stamps. Table T1 contains a time-stamp ts and values v for that.

我的表T2包含start_date和end_date，两个时间戳。表T1包含一个时间戳ts，值v。

I would like to use the pairs of time-stamps in T2 to obtain values v from T1 between those pair of dates. I tried something like below but it will not work. I would appreciate your help with pointing me to the problem.

我想用T2的时间戳来从这两个日期之间的T1获得值v。我试过下面的方法，但行不通。我希望你能帮助我指出问题所在。

select 
    v, 
    circle_ts 
from t1 
where circle_ts between start_ts 
    and end_ts in (
        select 
            start_ts, 
            end_ts 
        from t2 
        where meter_id = 10)

1 个解决方案

#1

You can alternatively JOIN both tables and searched on for values that is between with dates.

您也可以加入这两个表，并搜索与日期之间的值。

SELECT  DISTINCT a.v, a.circle_ts 
FROM    t1 a
        INNER JOIN t2 b
            ON a.circle_ts BETWEEN b.start_ts AND b.end_ts
WHERE   b.meter_id = 10