如何从指定的id开始获取组

Question

如何获得以id开头的一组kol_name，该id在包含“status”=“start”时获得结束。它的意思是，我从外部获得'id‘，并希望获得一组列，从'start’包含，到下一个'start‘，或者最后一个’add‘，结果在动态SQL中使用。

id      kol_name      status
------------------------------
44     kol_name_1      add
43     kol_name_2      add
42     kol_name_3      add  
41     kol_name_4     start 
40     kol_name_5      add
39     kol_name_6     start 
37     kol_name_7     start 

我的预期输出是当我得到id 44时

kol_name
--------------------
kol_name_1      
kol_name_2      
kol_name_3       
kol_name_4     

我的预期输出是当我得到id 37时

kol_name
--------------------   
kol_name_7     

我的预期输出是当我得到id 39

kol_name
--------------------   
kol_name_5 
kol_name_6    

我怎么能这么做？

Answer 1

嗯。一种方法是计算“给定id”之后的“第一个开始”(“之后”似乎是一个较小的id，这就是为什么我把它放在引号中)。然后，您可以使用简单的逻辑：

select t.*
from t cross join
     (select max(id) as startid
      from t
      where id <= 44 and status = 'start'
     ) tt
where t.id <= 44 and t.id >= tt.startid;

如果您只想在查询中提及"44“一次：

select t.*
from t cross join
     (select theid, max(id) as startid
      from t cross join (select 44 as theid from dual)
      where id <= theid and status = 'start'
     ) tt
where t.id <= tt.theid and t.id >= tt.startid;

编辑：

此代码只返回一行：

with t as (
    select 44 as id, 'kol_name_1' as kol_name, 'add' as status from dual union all
    select 43, 'kol_name_2', 'add' from dual union all
    select 42, 'kol_name_3', 'add' from dual union all
    select 41, 'kol_name_4', 'start' from dual union all
    select 40, 'kol_name_5', 'add' from dual union all
    select 39, 'kol_name_6', 'start' from dual union all
    select 37, 'kol_name_7', 'start' from dual 
   )
select t.*
from t cross join
     (select max(id) as startid
      from t
      where id <= 39 and status = 'start'
     ) tt
where t.id <= 39 and t.id >= tt.startid;

这里是SQL

编辑二；

哦，你想要两排39排。如果是这样的话，请使用不相等的方法更改startid的定义：

select t.*
from t cross join
     (select max(id) as startid
      from t
      where id < 44 and status = 'start'
     ) tt
where t.id <= 44 and t.id >= tt.startid;