选择行直到满足条件 [英] Select rows until condition met

问题描述

我想编写一个Oracle查询，该查询返回一组特定的信息.使用下表，如果给定一个id，它将返回ID和B的值.此外，如果B = T，它还将返回下一行.如果下一行具有B = T，它将返回该值，依此类推，直到遇到F.

I would like to write an Oracle query which returns a specific set of information. Using the table below, if given an id, it will return the id and value of B. Also, if B=T, it will return the next row as well. If that next row has a B=T, it will return that, and so on until a F is encountered.

因此，给定3，它将仅返回一行:(3，F).给定4它将返回3行:((4，T)，(5，T)，(6，F))

So, given 3 it would just return one row: (3,F). Given 4 it would return 3 rows: ((4,T),(5,T),(6,F))

id B
1 F
2 F
3 F
4 T
5吨
6 F
7吨
8楼

id B
1 F
2 F
3 F
4 T
5 T
6 F
7 T
8 F

提前谢谢！

推荐答案

使用子查询找出应该在什么位置停止，然后将所有行从起点返回到计算出的终点.

Use a sub-query to find out at what point you should stop, then return all row from your starting point to the calculated stop point.

SELECT
  *
FROM
  yourTable
WHERE
      id >= 4
  AND id <= (SELECT MIN(id) FROM yourTable WHERE b = 'F' AND id >= 4)

注意，这假设最后一条记录始终为'F'.您可以使用COALESCE处理最后一个记录为'T'.

Note, this assumes that the last record is always an 'F'. You can deal with the last record being a 'T' using a COALESCE.

SELECT
  *
FROM
  yourTable
WHERE
      id >= 4
  AND id <= COALESCE(
              (SELECT MIN(id) FROM yourTable WHERE b = 'F' AND id >= 4),
              (SELECT MAX(id) FROM yourTable                          )
            )

这篇关于选择行直到满足条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！