有如下表:
time_id next_time_id next_money
200801 200805 100
200802
200803
200804
200805 200810 200
200806
200807
现要求写一SQL语句更新上表,期望得到如下结果:
time_id next_time_id next_money
200801 200805 100
200802 200805 100
200803 200805 100
200804 200805 100
200805 200810 200
200806 200810 200
200807 200810 200
请高手帮忙看看。谢谢!
time_id next_time_id next_money
200801 200805 100
200802
200803
200804
200805 200810 200
200806
200807
现要求写一SQL语句更新上表,期望得到如下结果:
time_id next_time_id next_money
200801 200805 100
200802 200805 100
200803 200805 100
200804 200805 100
200805 200810 200
200806 200810 200
200807 200810 200
请高手帮忙看看。谢谢!
-------------------- -------------------- ----------
200801 200805 100
200802
200803
200804
200805 200810 2000
200806
200807已选择7行。OPER@tl> update test2 a
2 set (next_time_id,next_money)=(select next_time_id,next_money
3 from test2 b
4 where next_time_id is not null
5 and a.time_id<b.next_time_id
6 and rownum=1)
7 /已更新7行。OPER@tl> select * from test2;TIME_ID NEXT_TIME_ID NEXT_MONEY
-------------------- -------------------- ----------
200801 200805 100
200802 200805 100
200803 200805 100
200804 200805 100
200805 200810 2000
200806 200810 2000
200807 200810 2000已选择7行。OPER@tl>
一句SQL也行,不过我想的方法太复杂了,不简洁,想必效率也不会高.
--data:
create table sa
(
s01 nvarchar2(20)
,s02 nvarchar2(10)
,s03 int
)insert into sa values('200801','200805',100);
insert into sa values('200802','',0);
insert into sa values('200803','',0);
insert into sa values('200804','',0);
insert into sa values('200805','200810',200);
insert into sa values('200806','',0);
insert into sa values('200807','',0);
insert into sa values('200810','200812',300);
insert into sa values('200811','',0);
commit; --sql:
select sa.s01,min(sb.s02),min(sb.s03) from
sa,
(select s01,s02,s03 from sa
where s02 is not null
and s01<s02 ) sb
where sa.s01<sb.s02
group by sa.s01--result:
200801 200805 100
200802 200805 100
200803 200805 100
200804 200805 100
200805 200810 200
200806 200810 200
200807 200810 200
200810 200812 300
200811 200812 300
意思就是说200805这列的next_time_id不能大于200807,因为在200809就有新的next_time_id了.
不过看列名,估计不会有这种情况.