怎样用一条SQL获取同期值和前期值

[vanjayhsu]

测试数据如下：
<code>
create table test(time_id varchar2(6),value number);
insert into test
select '200801',1 from dual union all
select '200802',2 from dual union all
select '200701',3 from dual union all
select '200802',4 from dual union all
</code>
要求用一条SQL语句查询出同期值和前期值，预计得到的数据应如下：
<code>
time_id value lastmonth_value lastyear_value
200701 3
200702 4 3
200801 1 3
200802 2 1 4
</code>
求高手帮忙看看。谢谢！

[vanjayhsu]

请高手帮忙看看！谢谢！

[yf520gn]

select time_id, value,lag(value,1) over(partition by substr(time_id,1,4)order by time_id) as lastmonth_value,
lag(value,1) over(partition by substr(time_id,5,2) order by substr(time_id,1,4) ) as lastyear_value 
from c

[yf520gn]

有点错

select time_id, value,lag(value,1) over(partition by substr(time_id,1,4)order by time_id) as lastmonth_value,
lag(value,1) over(partition by substr(time_id,5,2) order by substr(time_id,1,4) ) as lastyear_value 
from test

[bw555]

提供的insert数据和你要的结果不一致呢？
select time_id,sum(value) as value,sum(lastmonth_value) as lastmonth_value,sum(lastyear_value) as lastyear_value
from(
select to_char(add_months(to_date(time_id,'yyyymm'),1),'yyyymm') as time_id,0 as value,value as lastmonth_value,0 as lastyear_value from test
union
select to_char(add_months(to_date(time_id,'yyyymm'),12),'yyyymm') as time_id,0 as value,0 as lastmonth_value,value as lastyear_value from test
union
select time_id,value,0,0 from test)
where time_id in (select time_id from test)
group by time_id
order by time_id

[sleepzzzzz]

楼上的算法很巧妙.

[vanjayhsu]

初步测试，2楼的好像可以，待我再多做几次测试。
3楼的意思不太明白。提供的insert语句应该要和我的结果保持一致。谢谢！

[dawugui]

select t.* , 
       (select value from tb where time_id = (select max(time_id) from tb where time_id < t.time_id))  lastmonth_value,  
       (select value from tb where time_id = (select time_id from tb where to_number(substr(time_id,1,4)) = to_number(substr(t.time_id,1,4)) - 1 and substr(time_id,5,2) = substr(t.time_id,5,2))) lastyear_value  
from tb t
order by t.time_id

[vanjayhsu]

请问，sleep，是说谁的算法很巧妙？谢谢！

[dawugui]

[code=SQL]--楼主给的测试数据错了一个.
create table tb(time_id varchar2(6),value number); 
insert into tb
  select '200801',1 from dual union all 
  select '200802',2 from dual union all 
  select '200701',3 from dual union all 
  select '200702',4 from dual 

select t.* , 
       (select value from tb where time_id = (select max(time_id) from tb where time_id < t.time_id))  lastmonth_value,  
       (select value from tb where time_id = (select time_id from tb where to_number(substr(time_id,1,4)) = to_number(substr(t.time_id,1,4)) - 1 and substr(time_id,5,2) = substr(t.time_id,5,2))) lastyear_value  
from tb t
order by t.time_id

drop table tb 

/*
TIME_I      VALUE LASTMONTH_VALUE LASTYEAR_VALUE
------ ---------- --------------- --------------
200701          3                               
200702          4               3               
200801          1               4              3
200802          2               1              4

4 rows selected.
*/

[/code]

[vanjayhsu]

回复dawugui,你的这个写法用测试数据测试不够准确哦。麻烦你检查一下。不过你倒提供了另外一种思路。对我很有帮助。

[dawugui]

[Quote=引用 10 楼 vanjayhsu 的回复:]
回复dawugui,你的这个写法用测试数据测试不够准确哦。麻烦你检查一下。不过你倒提供了另外一种思路。对我很有帮助。
[/Quote]
你的测试数据是错的.不是已经告诉你了?

[dawugui]

--你的
insert into test
select '200801',1 from dual union all
select '200802',2 from dual union all
select '200701',3 from dual union all
select '200802',4 from dual

--应该是
insert into test
select '200801',1 from dual union all
select '200802',2 from dual union all
select '200701',3 from dual union all
select '200702',4 from dual

[vanjayhsu]

呵呵，是的，当时输入太粗心了。谢谢！

[friendjin]

用oracle分析函数,网上搜搜,有10多个，多学学点知识吧