先用分析函数转换求得差值,存入临时表SELECT city, MONTH, val - lag(val, 1, 0) over(PARTITION BY t.city ORDER BY MONTH) FROM tt WHERE MONTH BETWEEN '201101' AND '201102';然后再用楼上链接的wildwave的通用行列转换过程处理
如果列比较简单,可以使用DECODE函数来转换
--一个比较笨的方法: SQL> with t as( 2 select '杭州' city,'201101' month,891 val from dual union all 3 select '北京','201101',659 from dual union all 4 select '上海','201101',361 from dual union all 5 select '广州','201101',654 from dual union all 6 select '杭州','201102',791 from dual union all 7 select '北京','201102',459 from dual union all 8 select '上海','201102',261 from dual union all 9 select '广州','201102',554 from dual) 10 , tb as( 11 select city,val 12 from ( 13 select city,month, 14 val-lag(val,1,0) over(partition by city order by month) val 15 from t 16 where month between '201101' and '201102') 17 where val<0) 18 select (select val from tb where city='杭州') "杭州", 19 (select val from tb where city='北京') "北京", 20 (select val from tb where city='上海') "上海", 21 (select val from tb where city='广州') "广州" 22 from dual 23 /
MONTH,
val - lag(val, 1, 0) over(PARTITION BY t.city ORDER BY MONTH)
FROM tt
WHERE MONTH BETWEEN '201101' AND '201102';然后再用楼上链接的wildwave的通用行列转换过程处理
--一个比较笨的方法:
SQL> with t as(
2 select '杭州' city,'201101' month,891 val from dual union all
3 select '北京','201101',659 from dual union all
4 select '上海','201101',361 from dual union all
5 select '广州','201101',654 from dual union all
6 select '杭州','201102',791 from dual union all
7 select '北京','201102',459 from dual union all
8 select '上海','201102',261 from dual union all
9 select '广州','201102',554 from dual)
10 , tb as(
11 select city,val
12 from (
13 select city,month,
14 val-lag(val,1,0) over(partition by city order by month) val
15 from t
16 where month between '201101' and '201102')
17 where val<0)
18 select (select val from tb where city='杭州') "杭州",
19 (select val from tb where city='北京') "北京",
20 (select val from tb where city='上海') "上海",
21 (select val from tb where city='广州') "广州"
22 from dual
23 /
杭州 北京 上海 广州
---------- ---------- ---------- ----------
-100 -200 -100 -100