mysql - 选择嵌套选择查询的输出和输出 + 'n' 之间的所有行

Question

我正在尝试简化我编写的查询，该查询将选择特定的序列号，例如 1，然后选择所有大于并等于输出序列号加“n”的行。例如选择序列号 1 和序列号 5 之间的所有行。数据集有 20k 行，所以我最初不会知道序列号。

select sequence,line_id,direction,stop_id,stop_name 
from bus_data.bus_locations as stoplist
where line_id ="39a" 
  and direction= "Outbound" 
  and sequence >=
                (SELECT sequence 
                FROM bus_data.bus_locations as depart
                where line_id = "39a"
                and stop_id= 786
                )
  and sequence <= 
               (SELECT sequence + 5 
                FROM bus_data.bus_locations as arrive
                where line_id = "39a"
                and stop_id= 786
                )
order by sequence;

我目前有一个有效的查询，但由于由同一张表中的三个选择组成，我觉得它效率不高。

Sequence  line_id  direction  stop_id  stop_name
19        39a      Outbound   786      Embassy of Malta
20        39a      Outbound   793      Dawson Street
21        39a      Outbound   1808     Morgan Place
22        39a      Outbound   7389     Navan Road
23        39a      Outbound   7586     National Gallery
24        39a      Outbound   7587     Science Gallery

我目前有一个有效的查询，但由于由同一张表中的三个选择组成，我觉得它效率不高。下表显示了上述查询的输出。任何不需要从整个表格中选择的建议都会非常有益。

标签： mysqlsql

Answer 1

如果我理解正确，您可以使用窗口函数：

select bl.*
from (select sequence, line_id, direction, stop_id, stop_name,
             max(case when stop_id = 786 then sequence end) over (partition by line_id) as the_sequence
      from bus_data.bus_locations bl
      where line_id = '39a' 
     ) bl
where sequence >= the_sequence and
      sequence <= the_sequence + 5;