有时候报表上面需要显示该笔操作的上一步骤或者下一步骤的详细信息，这个时候可以按照下面的做法：
先创建示例表：
--
Create table
create
table
LEAD_TABLE( CASEID
VARCHAR2
(
10
), STEPID
VARCHAR2
(
10
), ACTIONDATE DATE)tablespace COLM_DATA pctfree
10
initrans
1
maxtrans
255
storage ( initial 64K minextents
1
maxextents unlimited );
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step1
'
,to_date(
'
20070101
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step2
'
,to_date(
'
20070102
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step3
'
,to_date(
'
20070103
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step4
'
,to_date(
'
20070104
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step5
'
,to_date(
'
20070105
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step4
'
,to_date(
'
20070106
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step6
'
,to_date(
'
20070101
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case1
'
,
'
Step1
'
,to_date(
'
20070201
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case2
'
,
'
Step2
'
,to_date(
'
20070202
'
,
'
yyyy-mm-dd
'
));
insert
into
LEAD_TABLE
values
(
'
Case2
'
,
'
Step3
'
,to_date(
'
20070203
'
,
'
yyyy-mm-dd
'
));
commit
;
每一条记录都能连接到上/下一行的内容
select
caseid,stepid,actiondate,lead(stepid)
over
(partition
by
caseid
order
by
actiondate) nextstepid,lead(actiondate)
over
(partition
by
caseid
order
by
actiondate) nextactiondate,lag(stepid)
over
(partition
by
caseid
order
by
actiondate) prestepid,lag(actiondate)
over
(partition
by
caseid
order
by
actiondate) preactiondate
from
lead_table
结果如下：
Case1 Step1
2007
-
1
-
1
Step2
2007
-
1
-
2
Case1 Step2
2007
-
1
-
2
Step3
2007
-
1
-
3
Step1
2007
-
1
-
1
Case1 Step3
2007
-
1
-
3
Step4
2007
-
1
-
4
Step2
2007
-
1
-
2
Case1 Step4
2007
-
1
-
4
Step5
2007
-
1
-
5
Step3
2007
-
1
-
3
Case1 Step5
2007
-
1
-
5
Step4
2007
-
1
-
6
Step4
2007
-
1
-
4
Case1 Step4
2007
-
1
-
6
Step6
2007
-
1
-
7
Step5
2007
-
1
-
5
Case1 Step6
2007
-
1
-
7
Step4
2007
-
1
-
6
Case2 Step1
2007
-
2
-
1
Step2
2007
-
2
-
2
Case2 Step2
2007
-
2
-
2
Step3
2007
-
2
-
3
Step1
2007
-
2
-
1
Case2 Step3
2007
-
2
-
3
Step2
2007
-
2
-
2
还可以进一步统计一下两者的相差天数
select
caseid,stepid,actiondate,nextactiondate,nextactiondate
-
actiondate datebetween
from
(
select
caseid,stepid,actiondate,lead(stepid)
over
(partition
by
caseid
order
by
actiondate) nextstepid,lead(actiondate)
over
(partition
by
caseid
order
by
actiondate) nextactiondate,lag(stepid)
over
(partition
by
caseid
order
by
actiondate) prestepid,lag(actiondate)
over
(partition
by
caseid
order
by
actiondate) preactiondate
from
lead_table)
结果如下：
Case1 Step1
2007
-
1
-
1
2007
-
1
-
2
1
Case1 Step2
2007
-
1
-
2
2007
-
1
-
3
1
Case1 Step3
2007
-
1
-
3
2007
-
1
-
4
1
Case1 Step4
2007
-
1
-
4
2007
-
1
-
5
1
Case1 Step5
2007
-
1
-
5
2007
-
1
-
6
1
Case1 Step4
2007
-
1
-
6
2007
-
1
-
7
1
Case1 Step6
2007
-
1
-
7
Case2 Step1
2007
-
2
-
1
2007
-
2
-
2
1
Case2 Step2
2007
-
2
-
2
2007
-
2
-
3
1
Case2 Step3
2007
-
2
-
3
