我正在尝试简化我编写的一个查询，该查询将选择一个特定的序列号，例如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
我目前有一个可以工作的查询，但感觉它效率不高，因为它由来自同一个表的三个选择组成。 下表显示了上述查询的输出。 任何不需要从整张表中选择的建议都是非常有益的。
如果我理解正确，你可以使用窗口函数:
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;
