如何列出给定子句中没有空格的最新系列?

yqlxgs2m  于 2021-07-27  发布在  Java
关注(0)|答案(1)|浏览(283)

给出下表示例:

+-----------+
| Id | Name |  
+----+------+
| 1  | A    |
| 2  | B    |
| 3  | B    |
| 4  | C    |
| 5  | A    |
| 6  | B    |
| 7  | B    |
| 8  | B    |
| 9  | B    |
| 10 | X    |
+----+------+

我希望通过查询获得以下结果:

+----+------+
| 6  | B    |
| 7  | B    |
| 8  | B    |
| 9  | B    |
+----+------+

我能做的最好的查询是:

SELECT * FROM 
 (SELECT id, name, LEAD(id) OVER (ORDER BY id) t 
  FROM test WHERE name = 'B' ORDER BY id) 
WHERE ID <> t-1;

在这里摆弄

nukf8bse

nukf8bse1#

如果需要长度和起始位置:

select min(id), max(id)
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by name order by id) as seqnum_1
      from test t
     ) t
where name = 'B'
group by (seqnum - seqnum_1)
order by min(id) desc
fetch first 1 row only;

可以联接回表以获取原始行。
另一种方法是使用窗口函数来计算给定行后的非B数。然后选择第一个:

select t.*
from (select t.*,
             dense_rank() over (order by nonbs_after asc) as grp
      from (select t.*, 
                   sum(case when name <> 'B' then 1 else 0 end) over (order by id desc) as nonbs_after
            from test t
           ) t
      where name = 'B'
     ) t
where grp = 1;

这是一把小提琴。

相关问题