mysql - SQL join a table with exact time or before it

Question

I would like to get a results table based on table2 with potential values not found in the table1. For example lets say I have table1 with

time | col1 | col2
 10  |  a   |  b
 15  |  c   |  d
 23  |  e   |  f
 45  |  g   |  h
 61  |  i   |  j
 70  |  k   |  l

and table2 with

 time 
 8    
 23   
 55  
 72 

results should be

time | col1 | col2
 8   | null |  null
 23  |  e   |  f
 55  |  g   |  h
 72  |  k   |  l

One way to look at the example is like a time table. Say a time table of a president or a ruler. So in year/time 10 a b became the ruler. At year/time 8, there is no ruler so its null. At year/time 55, g h should still be the ruler.

标签： mysqlsql

Answer 1

You can use left join . . . along with lead() to get the end time:

select t2.*, t1.col1, t1.col2
from table2 t2 left join
     (select t1.*, lead(time) over (order by time) as next_time
      from table1 t1
     ) t1
     on t2.time >= t1.time and
        (t2.time < t1.next_time or t1.next_time is null);