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

yqlxgs2m 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(281)

给出下表示例：

+-----------+
| 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;

在这里摆弄

sql oracle Aggregation gaps-and-islands

来源：https://stackoverflow.com/questions/63160820/how-to-list-the-latest-series-with-no-gaps-of-a-given-clause

1条答案

按热度按时间

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;

这是一把小提琴。

赞(0）回复(0）举报 2021-07-27

我来回答

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

1条答案

相关问题

热门标签

最新问答