测试数据如下:
<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>
求高手帮忙看看。谢谢!
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