Oracle if null is displayed on a value-CodePudding

Sample list as follows, query out such as the six figures, but in the middle of free to achieve when the field is empty shows the value of an empty
To achieve the style field
A. A
A. A
A
B B
B
B
C C

CodePudding user response:

Lag and lead these two functions, just meet your need,

CodePudding user response:

Lag, lead on these two functions can obtain a or a data values, but your this requirement, because of the continuous null values, should not apply,
First of all, on a sure can't be random, to have a sort field, such as a certain time,
I think about train of thought should be in this way,
WITH
T1 AS (SELECT get fields FROM the table name ORDER BY sorting fields),
T2 AS (SELECT ROWNUM N, obtain the field the FROM T1)
Field, SELECT NVL ((SELECT TT. Access to the field of the FROM T2 WHERE TT TT. N<=T.N AND TT. Access to the field IS NOT NULL AND ROWNUM=1)) FROM T2 T

CodePudding user response:

Don't bicker, upstairs lag or lead (field ignore crosstab), is achievable,

A simple example to

 
SQL> The create table t1 (id int primary key, name varchar (10)); 

The Table created 
SQL> The begin 
Insert into t1 values (1, 'A'); 
3 insert into t1 values (2, null); 
4 insert into t1 values (3, null); 
5 the insert into t1 values (4, 'B'); 
6 insert into t1 values (5, null); 
7 the insert into t1 values (6, null); 
8 the insert into t1 values (7, null); 
Insert into t1 values (8, 'D'); 
10 insert into t1 values (9, 'G'); 
11 end; 
12/

PL/SQL procedure successfully completed 
SQL> Select id, name, NVL (name, lag (name ignore crosstab) over (order by id) xa 
2 the from t1; 

ID NAME XA 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 A A 
2 A 
3 A 
4 B B 
5 B 
6 B 
7 B 
8 D D 
9 G G 

9 rows selected 
SQL> Drop table t1 purge; 

Table dropped 

SQL>