偏移量函数进阶一oracle空行取上一行

2022-01-17 08:21:08 浏览数 (2)

Oracle

场景

excle的合并列导入数据库中有空行出现

测试数据

代码语言：javascript复制

create table test_IGNORE as
select '商业银行' as rclass,'招商银行' as bank , 1 as id from dual 
union all 
select '','浦发银行' , 2 as id from dual 
union all 
select '','中信银行' , 3 as id from dual 
union all 
select '','广发银行' , 4 as id from dual 
union all 
select '','光大银行' , 5 as id from dual 
union all 
select '','平安银行' , 6 as id from dual

使用LAG函数

代码语言：javascript复制

SELECT NVL(T.RCLASS,LAG(T.RCLASS, 1) OVER(ORDER BY T.ID)) AS RCLASS,
       T.BANK,
       T.ID
FROM TEST_IGNORE T;

增加ignore nulls

代码语言：javascript复制

SELECT NVL(T.RCLASS,LAG(T.RCLASS IGNORE NULLS, 1) OVER(ORDER BY T.ID)) RCLASS,
       T.BANK,
       T.ID
FROM TEST_IGNORE T;

本站文章除注明转载/出处外，均为本站原创，转载前请务必署名,转载请标明出处最后编辑时间为: 2021/11/17 12:31:11

数据库 oracle lag

0 人点赞