将第 n 行与第 n+1 行进行比较，如果它在第 n 行的范围内，则打印 n+1 行 USNG ORACLE QUERY [英] Compare nth row with n+1 th row and if it lies in range of n th row print n+1 row USNG ORACLE QUERY

问题描述

我有一张像

ID     name   Start_no     End_no
1       a       2           123  
2       b       3           65
3       c       191         199
4       d       201         225
5       e       220         223
6       f       221         224

我试过了:

SELECT  *   
  FROM IV_STOCK_DETAILS a
 where  a.STOCK_START_NO in ( select b.stock_end_no
                                from IV_STOCK_DETAILS B
                               where b.stock_start_no between a.stock_start_no 
                                                          AND A.STOCK_END_NO 
                                 AND B.STOCK_END_NO BETWEEN a.stock_start_no 
                                                        AND A.STOCK_END_NO  
                                 AND (a.stock_start_no - A.STOCK_END_NO) !=0  
                            )
ORDER BY cod_stock_branch, stock_start_no;

所以我需要将所有行与其前一行一一检查，如果它的 start_no 和 end_no 位于 start_no 和 内>end_no 前一个打印行.在这种情况下，答案将是第 2、5、6 行......我只需要查询.

so I need to check all rows one by one with its previous rows and if its start_no and end_no lies within start_no and end_no of previous one print row. In this case answer will be rows no 2, 5, 6.. I need only query.

推荐答案

这是一个使用 LAG 函数或 Oracle 的解决方案，它可以让您访问上一行.

Here is a solution using LAG function or Oracle, which gives you access to the previous row.

而且，根据您的要求，您应该只从表中获取第 2 行和第 5 行，因为第 6 行的 end_no 为 224，不小于 end_no 即 223

And, you should only get row 2 and 5 from your table, as per the requirements you have, since row 6 has end_no as 224, which is not less than end_no of row5 which is 223

SELECT id, name, start_no, end_no 
FROM (
    SELECT id, name, start_no, end_no, 
           lag(start_no, 1, 0) over (order by id) prev_start_no ,
           lag(end_no, 1, 0) over (order by id) prev_end_no
    FROM test
) 
WHERE start_no BETWEEN prev_start_no AND prev_end_no
AND end_no BETWEEN prev_start_no AND prev_end_no;

这篇关于将第 n 行与第 n+1 行进行比较，如果它在第 n 行的范围内，则打印 n+1 行 USNG ORACLE QUERY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！