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

vanjayhsu 2008-12-10 01:56:37
测试数据如下:
<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>
求高手帮忙看看。谢谢!
...全文
155 点赞 收藏 14
写回复
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
friendjin 2008-12-13
用oracle分析函数,网上搜搜,有10多个,多学学点知识吧
回复
vanjayhsu 2008-12-10
呵呵,是的,当时输入太粗心了。谢谢!

回复
dawugui 2008-12-10
--你的
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
回复
dawugui 2008-12-10
[Quote=引用 10 楼 vanjayhsu 的回复:]
回复dawugui,你的这个写法用测试数据测试不够准确哦。麻烦你检查一下。不过你倒提供了另外一种思路。对我很有帮助。
[/Quote]
你的测试数据是错的.不是已经告诉你了?
回复
vanjayhsu 2008-12-10
回复dawugui,你的这个写法用测试数据测试不够准确哦。麻烦你检查一下。不过你倒提供了另外一种思路。对我很有帮助。
回复
dawugui 2008-12-10
[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 2008-12-10
请问,sleep,是说谁的算法很巧妙?谢谢!
回复
dawugui 2008-12-10
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 2008-12-10
初步测试,2楼的好像可以,待我再多做几次测试。
3楼的意思不太明白。提供的insert语句应该要和我的结果保持一致。谢谢!
回复
sleepzzzzz 2008-12-10
楼上的算法很巧妙.
回复
bw555 2008-12-10
提供的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
回复
yf520gn 2008-12-10
有点错

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
回复
yf520gn 2008-12-10

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
回复
vanjayhsu 2008-12-10
请高手帮忙看看!谢谢!
回复
发动态
发帖子
Oracle 高级技术
创建于2007-09-28

3395

社区成员

Oracle 高级技术相关讨论专区
申请成为版主
社区公告
暂无公告