去除第一个空值的方法。first_value 函数,wm_concat自动忽略Null
with test as
(select 1 col1, null col2 from dual union all
select 2, null from dual union all
select 0, 2 from dual)
select substr (wmsys.wm_concat(col1), 1, 1), substr(wmsys.wm_concat(col2), 1, 1)
from test b
order by col1;
------上面这个是把所有同列的值拼成一个字符串, 然后取第一个字符
with test as(
select 1 col1, null col2, 1 rowindex from dual union all
select 2 , null , 2 from dual union all
select 0 , 2 , 3 from dual
)
select *
from (select first_value(col1 ignore nulls) over() col1,
first_value(col2 ignore nulls) over() col2,
row_number() over(order by rowindex) ttt
from test
) b
where ttt = 1
----------上面这个是取第一行的值,但是 ignore null