比如我有张表 ID columnA columnB columnC ....
201101 88 58 69
201102 54 87 99
.
.有何简便的方法 直接取出 ID 201102 - ID201101 所有 列 columnA columnB columnC .... 的值
就是 想得到以下结果 ID columnA columnB columnC ....
-34 29 30
麻烦各位大大指点下 谢谢
201101 88 58 69
201102 54 87 99
.
.有何简便的方法 直接取出 ID 201102 - ID201101 所有 列 columnA columnB columnC .... 的值
就是 想得到以下结果 ID columnA columnB columnC ....
-34 29 30
麻烦各位大大指点下 谢谢
(select 201101 ID ,88 columnA ,58 columnB ,69 columnC from dual
union
select 201102 ,54, 87, 99 from dual)
select t2.a2 - t1.a1,t2.b2 - t1.b1,t2.c2 - t1.c1 from
( select ID ,columnA a1,columnB b1,columnC c1 from t where id = 201101 ) t1
,( select ID,columnA a2,columnB b2,columnC c2 from t where id = 201102 ) t2;
columnA - lag(columnA) over(ORDER BY id),
columnB - lag(columnB) over(ORDER BY id),
columnC - lag(columnC) over(ORDER BY id)
FROM tab1;
(select 201101 ID ,88 columnA ,58 columnB ,69 columnC from dual
union
select 201102 ,54, 87, 99 from dual)
select t2.a2 - t1.a1,t2.b2 - t1.b1,t2.c2 - t1.c1 from
( select ID ,columnA a1,columnB b1,columnC c1 from t where id = 201101 ) t1
,( select ID,columnA a2,columnB b2,columnC c2 from t where id = 201102 ) t2;
不过这样的操作实在是没有什么意义呀
SQL> with t as(
2 select '201101' id,88 c1,58 c2,69 c3 from dual union all
3 select '201102',54,87,99 from dual union all
4 select '201103',64,97,100 from dual)
5 select id
6 ,c1 - lag(c1,1,0) over (order by id) c1
7 ,c2 - lag(c2,1,0) over (order by id) c2
8 ,c3 - lag(c3,1,0) over (order by id) c3
9 from t
10 /
ID C1 C2 C3
------ ---------- ---------- ----------
201101 88 58 69
201102 -34 29 30
201103 10 10 1