在数据库表中，根据一列值获取记录，而该值则基于其他两列。

Question

我有下表：

 username | timestamp   | distance_location1 | distance_location2
  jack     1532101253000   22.2541(km)           152.652(km)

和我使用的查询是

 select * 
 from   table 
 where  timestamp >= (select min(distance_location1) from table 
   AND  timestamp <= (select min(distance_location2) from table

我想得到基于时间戳列的记录。

时间戳的起始值是最小distance_location1的位置。

时间戳的结束值是最小distance_location2的位置。

上面的查询无法工作，因为它提供了0条记录。

Answer 1

如果distance_location1是数据类型是时间戳，那么下面的查询将在您将括号放在错误位置的地方工作。

select * 
     from   table 
     where  timestamp >=
      ( select min(distance_location1 from table )
       AND  timestamp <= (select min(distance_location2 from table)

但是如果distance_location1和distance_location2不是数据类型时间戳

select * from table
where timestamp>= (select min(timestamp)  from table t where t.distance_location1=(select min(distance_location1) from table)
) and

 timestamp<=( select min(timestamp)  from table t where t.distance_location2=(select min(distance_location2) from table))

Answer 2

你需要比较时间戳，而不是距离。在Oracle中，您可以使用keep和分析函数：

select t.* 
from (select t.*,
             min(timestamp) keep (dense_rank first over order by distance_location1) as distance_location1_timestamp,
             min(timestamp) keep (dense_rank first over order by distance_location2) as distance_location2_timestamp
      from table t
     ) t
where timestamp >= distance_location1_timestamp and
      timestamp <= distance_location2_timestamp;