测试数据如下:
<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>
求高手帮忙看看。谢谢!
<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>
求高手帮忙看看。谢谢!
解决方案 »
- 很纠结这个问题,大家帮帮忙。
- 执行插入数据时出现enter value for什么意思?
- 我在导入一个DMP文件后出现一些错误
- 如何查找一个字段的值包含了另一个字段的值
- oracle中的聚合问题
- 数据库启动不起来啊!
- OEM和OMS,在线等答案,次问题我已经是第三次提出,但是没有一个高手... 还请高手们...
- 关于10g触发器中:new 和 :old 的用法问题?? 大侠请进,分不够再加!!
- 急!! 我的oracle9i for Linux是cpio.gz格式的, 请教如何安装?
- oracle trigger 报错!!!求教
- 100分求救,我把Oracle 的一个用户删除了,又导入了新的数据,如何恢复上次删除的用户的表结构啊!!!!!!!!!!!!!!????????
- Asp.NEt对Oracle包的访问问题
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
lag(value,1) over(partition by substr(time_id,5,2) order by substr(time_id,1,4) ) as lastyear_value
from test
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
3楼的意思不太明白。提供的insert语句应该要和我的结果保持一致。谢谢!
(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
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_iddrop table tb /*
TIME_I VALUE LASTMONTH_VALUE LASTYEAR_VALUE
------ ---------- --------------- --------------
200701 3
200702 4 3
200801 1 4 3
200802 2 1 44 rows selected.
*/
[/code]
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