oracle 如何实现上一条、下一条、查找不连续的值（转）

来源：互联网发布：ae软件都能做什么编辑：程序博客网时间：2024/05/16 02:07

第一部分：oracle 如何实现上一条、下一条

select z.p from (
select y.org_id,lag(y.org_id,1,0) over (order by y.org_id) as p from t_org y ) z
where org_id= '34010100'

select z.p from (
select y.evidence_id,lead(y.evidence_id,1,0) over (order by y.evidence_id) as p from TEMP_VIO_EVIDENCE y where 1=1 ) z
where z.evidence_id= '34010100'

lead 方法的说明：

lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

value_expr 值表达式，通常是字段，也可是是表达式。

offset 偏移，如果>0 表示与当前行相比，向前的行数。默认值为1

default 默认值，偏移结果不存在时，默认的返回值。

第二部分查找不连续的值

偶然一次接触oracle 的分析函数lead，发现很好用，在此记录。

需求很简单，找出不连续的值

createtable test(idintprimarykey);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
insert into test values(5);
insert into test values(6);
insert into test values(8);
insert into test values(9);
insert into test values(11);
insert into test values(12);
insert into test values(17);

用lead分析函数实现：

select id1, id2 from(
SELECT id id1, lead(id,1) over (order by id) as id2 from test)
where id2-id1>1;

lead 方法的说明：

lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)

value_expr 值表达式，通常是字段，也可是是表达式。

offset 偏移，如果>0 表示与当前行相比，向前的行数，同理，<0表示向后。默认值为1

default 默认值，无偏移结果的默认值。

over()oracle分析函数中的标准方法。

其他：如果求连续的值，则：

select min(id),max(id) from (select id,rownum,id-rownum rn from (select id from test order by id))
group by rn having count(rn) > 1

如何计算连续的最大值

如表：

CREATE TABLE t1 AS
(SELECT 'a' AS NAME, 1 AS seq
    FROM dual
UNION ALL
SELECT 'a' AS NAME, 2 AS seq
    FROM dual
UNION ALL
SELECT 'a' AS NAME, 3 AS seq
    FROM dual
UNION ALL
SELECT 'a' AS NAME, 5 AS seq
    FROM dual
UNION ALL
SELECT 'b' AS NAME, 1 AS seq
    FROM dual
UNION ALL
SELECT 'b' AS NAME, 2 AS seq
    FROM dual
UNION ALL
SELECT 'b' AS NAME, 4 AS seq
    FROM dual
UNION ALL
SELECT 'a' AS NAME, 6 AS seq
    FROM dual);

name seq

a          1
a          2
a          3
a          5
b         1
b          2
b          4
a          6

实现每个连续的最大值

SELECT NAME, MIN(seq), MAX(seq), MIN(ROWID)
FROM (SELECT row_number() over(ORDER BY ROWID) - seq AS sn2, NAME, seq
FROM t1)
GROUP BY NAME, sn2
ORDER BY 4;

a 3
a 5
b 2
b 4
a 6

实现所有连续的最大值

select name, min(seq) as seq
from (select name,
               seq,
               lag(seq, 1) over(partition by name order by name, seq desc) - seq as rn
          from t1)
where rn > 1
group by name;

a 3
b 2