具体如下:
SQL> select * from table1;
MONTH_ID DEPT_ID N_NUMBER
---------- ---------- ----------
200906 1 100
200906 2 50
200906 3 40
200907 1 150
200907 2 80
200907 4 60
6 rows selected
SQL>
SQL> select tmp2.dept_id,
2 nvl(tmp1.increase_rate, -999) increase_rate
3 from
4 (select month_id,
5 dept_id,
6 increase_rate
7 from
8 (select month_id,
9 dept_id,
10 case
11 when month_id = 200907 and lag(n_number) over(partition by dept_id order by month_id) is null and n_number is not null then
12 999
13 when month_id = 200907 and lag(n_number) over(partition by dept_id order by month_id) is not null and n_number is not null then
14 (n_number - lag(n_number) over(partition by dept_id order by month_id)) / lag(n_number) over(partition by dept_id order by month_id)
15 end increase_rate
16 from table1)
17 where month_id = 200907) tmp1,
18 (select distinct dept_id from table1) tmp2
19 where tmp1.dept_id(+) = tmp2.dept_id
20 order by tmp2.dept_id;
DEPT_ID INCREASE_RATE
---------- -------------
1 0.5
2 0.6
3 -999
4 999
SQL>