可以用子查询的方式来实现,给你个参考,具体的你需要自己修改: with t as (select 1 c1, '370100' stcode, 356 locade, '2013-09' dt, 'jinan' areaname, 'jinxiuchuanshuiku' subname, 19.8 val from dual union all select 2 c1, '370100' stcode, 356 locade, '2013-10' dt, 'jinan' areaname, 'jinxiuchuanshuiku' subname, 20.3 val from dual) select stcode, locade, dt, areaname, subname, val "Oct", (select val from t where dt = '2013-09') "Sep" from t where dt = '2013-10';
用分析函数LAG实现 下面是一个例子: LAG 功能描述:可以访问结果集中的其它行而不用进行自连接。它允许去处理游标,就好像游标是一个数组一样。在给定组中可参考当前行之前的行,这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数,其默认值为1,若索引超出窗口的范围,就返回默认值(默认返回的是组中第一行),其相反的函数是LEAD SAMPLE:下面的例子中列prev_sal返回按hire_date排序的前1行的salary值 SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'PU_CLERK'; LAST_NAME HIRE_DATE SALARY PREV_SAL ------------------------- ---------- ---------- ---------- Khoo 18-5月 -95 3100 0 Tobias 24-7月 -97 2800 3100 Baida 24-12月-97 2900 2800 Himuro 15-11月-98 2600 2900 Colmenares 10-8月 -99 2500 2600
SELECT a.C0003_stcode,a.c0029_lcode,substr(to_char(a.c1432_date,'yyyy-mm-dd'),1,7) c1432_date, min(d.c0003_stname) stname ,min(sub.c0007_pname) subname ,(case a.C1432_DATE>=TO_DATE('2013-10-10','YYYY-MM-DD') and a.C1432_DATE<=TO_DATE('2013-10-17','YYYY-MM-DD') then avg(DECODE(a.C0001_ITEM_CODE,301,a.C1432_AVERAGE)) else 0 end) T10, ,(case a.C1432_DATE>=TO_DATE('2013-9-10','YYYY-MM-DD') and a.C1432_DATE<=TO_DATE('2013-9-17','YYYY-MM-DD') then avg(DECODE(a.C0001_ITEM_CODE,301,a.C1432_AVERAGE)) else 0 end) T9.... from table ...
楼上说的不错,可以考虑用LAG。
我这两个月的数据不是前后行,还有其他数据隔着呢,用lag只能是两行挨着吧
LAG(数值, 1, 0) OVER (PARTITION BY stname,subname ORDER BY 月份)
利用 PARTITION 分组就可以了
with t as (select 1 c1, '370100' stcode, 356 locade, '201309' dt, 'jinan' areaname, '锦绣山川水库' subname, 19.8 val from dual union all select 2 c1, '370100' stcode, 356 locade, '201310' dt, 'jinan' areaname, '锦绣山川' subname, 20.3 val from dual union all select 2 c1, '370100' stcode, 356 locade, '201309' dt, 'jinan' areaname, '锦绣' subname, 23.3 val from dual union all select 2 c1, '370100' stcode, 356 locade, '201310' dt, 'jinan' areaname, '锦绣山川水库' subname, 24.7 val from dual union all select 2 c1, '370100' stcode, 356 locade, '201309' dt, 'jinan' areaname, '锦绣山川' subname, 21.4 val from dual union all select 2 c1, '370100' stcode, 356 locade, '201310' dt, 'jinan' areaname, '锦绣' subname, 18.1 val from dual) --SELECT * FROM t ORDER BY subname,dt; SELECT areaname,subname,LAG(val,1,0) OVER(PARTITION BY subname ORDER BY dt) "上月",val FROM t; 结果如图,怎么去重啊???
with t as
(select 1 c1,
'370100' stcode,
356 locade,
'2013-09' dt,
'jinan' areaname,
'jinxiuchuanshuiku' subname,
19.8 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'2013-10' dt,
'jinan' areaname,
'jinxiuchuanshuiku' subname,
20.3 val
from dual)
select stcode,
locade,
dt,
areaname,
subname,
val "Oct",
(select val from t where dt = '2013-09') "Sep"
from t
where dt = '2013-10';
下面是一个例子:
LAG 功能描述:可以访问结果集中的其它行而不用进行自连接。它允许去处理游标,就好像游标是一个数组一样。在给定组中可参考当前行之前的行,这样就可以从组中与当前行一起选择以前的行。Offset是一个正整数,其默认值为1,若索引超出窗口的范围,就返回默认值(默认返回的是组中第一行),其相反的函数是LEAD
SAMPLE:下面的例子中列prev_sal返回按hire_date排序的前1行的salary值
SELECT last_name, hire_date, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS prev_sal FROM employees WHERE job_id = 'PU_CLERK';
LAST_NAME HIRE_DATE SALARY PREV_SAL
------------------------- ---------- ---------- ----------
Khoo 18-5月 -95 3100 0
Tobias 24-7月 -97 2800 3100
Baida 24-12月-97 2900 2800
Himuro 15-11月-98 2600 2900
Colmenares 10-8月 -99 2500 2600
min(d.c0003_stname) stname
,min(sub.c0007_pname) subname ,(case a.C1432_DATE>=TO_DATE('2013-10-10','YYYY-MM-DD')
and a.C1432_DATE<=TO_DATE('2013-10-17','YYYY-MM-DD')
then avg(DECODE(a.C0001_ITEM_CODE,301,a.C1432_AVERAGE)) else 0 end) T10,
,(case a.C1432_DATE>=TO_DATE('2013-9-10','YYYY-MM-DD')
and a.C1432_DATE<=TO_DATE('2013-9-17','YYYY-MM-DD')
then avg(DECODE(a.C0001_ITEM_CODE,301,a.C1432_AVERAGE)) else 0 end) T9....
from table ...
(select 1 c1,
'370100' stcode,
356 locade,
'201309' dt,
'jinan' areaname,
'锦绣山川水库' subname,
19.8 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'201310' dt,
'jinan' areaname,
'锦绣山川' subname,
20.3 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'201309' dt,
'jinan' areaname,
'锦绣' subname,
23.3 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'201310' dt,
'jinan' areaname,
'锦绣山川水库' subname,
24.7 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'201309' dt,
'jinan' areaname,
'锦绣山川' subname,
21.4 val
from dual
union all
select 2 c1,
'370100' stcode,
356 locade,
'201310' dt,
'jinan' areaname,
'锦绣' subname,
18.1 val
from dual)
--SELECT * FROM t ORDER BY subname,dt;
SELECT areaname,subname,LAG(val,1,0) OVER(PARTITION BY subname ORDER BY dt) "上月",val
FROM t;
结果如图,怎么去重啊???