oracle sql 资产类别组合,Oracle SQL-从单行的列组合派生多行

这是一种方法。

不过,我没有对此使用分层查询。

create table t(personId int,lastEvent int, currentEvent int, nextEvent int);

insert into t values(1,null,1,2);

insert into t values(1,1,2,3);

insert into t values(1,2,3,4);

insert into t values(1,3,4,null);

select a.personId,case when b.rn=1 then

a.lastEvent

else a.currentEvent

end as event1

,case when b.rn=1 then

a.currentEvent

else a.nextEvent

end as event2

,case when a.nextEvent is not null then

a.currentEvent

end as currentEvent

from t a

join (select rownum as rn from all_objects where rownum<=2) b

on case when a.nextEvent is not null then 2

else 1

end >=b.rn

order by 1,4,3

+----------+--------+--------+--------------+

| PERSONID | EVENT1 | EVENT2 | CURRENTEVENT |

+----------+--------+--------+--------------+

| 1 | - | 1 | 1 |

| 1 | 1 | 2 | 1 |

| 1 | 1 | 2 | 2 |

| 1 | 2 | 3 | 2 |

| 1 | 2 | 3 | 3 |

| 1 | 3 | 4 | 3 |

| 1 | 3 | 4 | - |

+----------+--------+--------+--------------+