代码之家 › 专栏 › 技术社区 › usersam

Oracle LEAD-返回下一个匹配的列值

lead oracle11g sql

0

usersam · 技术社区 · 6 年前

我想从out列得到下一个out数据。所以在下面的查询中使用了LEAD函数。

SELECT ROW_NUMBER,TIMESTAMP,IN,OUT,LEAD(OUT) OVER (PARTITION BY NULL ORDER BY TIMESTAMP) AS NEXT_OUT 
FROM MYTABLE;

但是我需要知道匹配的next column值,就像需要的列一样。请告诉我如何在Oracle LEAD功能中实现这一点

谢谢

1 回复 | 直到 6 年前

1

Salman Arshad 6 年前

将行号分别分配给所有输入和输出,将结果放在一列中进行排序,并计算潜在客户:

WITH cte AS (
    SELECT t.*
         , CASE WHEN "IN"  IS NOT NULL THEN COUNT("IN")  OVER (ORDER BY "TIMESTAMP") END AS rn1
         , CASE WHEN "OUT" IS NOT NULL THEN COUNT("OUT") OVER (ORDER BY "TIMESTAMP") END AS rn2
    FROM t
)
SELECT cte.*
     , LEAD("OUT") OVER (ORDER BY COALESCE(rn1, rn2), rn1 NULLS LAST) AS NEXT_OUT
FROM cte
ORDER BY COALESCE(rn1, rn2), rn1 NULLS LAST

Demo on db<>fiddle

2

1

Gordon Linoff 6 年前

在“in”和“out”中枚举并使用该信息进行匹配。

select tin.*, tout.out as next_out
from (select t.*,
             count(in) over (order by timestamp) as seqnum_in
      from t
     ) tin left join
      (select t.*,
             count(out) over (order by timestamp) as seqnum_out
      from t
     ) tout
     on tin.in is not null and
        tout.out is not null and
        tin.seqnum_in = tout.seqnum_out;