对不起,日期写错了
大侠:
我在数据库当中有如下记录
RQ NAME NUM2
2003-01-01 A 5
2003-01-02 B
2003-01-03 c
2003-01-04 d 31
2003-01-05 e 28
2003-01-06 f
2003-01-07 g
2003-01-08 j
2003-01-09 i 31
2003-01-10 j
. . .
.
.
.
现在我想通过QUERY1.SQL语句查处所有记录,但是有的记录的NUM字段时空的
那么这调记录的NUM数值应该去距离当前记录日期最近的并且NUM2是有值的记录
执行SELECT 之后
希望时
2003-01-01 A 5
2003-01-02 B 5
2003-01-03 c 5
2003-01-04 d 31
2003-01-05 e 28
2003-01-06 f 28
2003-01-07 g 28
2003-01-08 j 28
2003-01-09 i 31
2003-01-10 j 31
大侠:
我在数据库当中有如下记录
RQ NAME NUM2
2003-01-01 A 5
2003-01-02 B
2003-01-03 c
2003-01-04 d 31
2003-01-05 e 28
2003-01-06 f
2003-01-07 g
2003-01-08 j
2003-01-09 i 31
2003-01-10 j
. . .
.
.
.
现在我想通过QUERY1.SQL语句查处所有记录,但是有的记录的NUM字段时空的
那么这调记录的NUM数值应该去距离当前记录日期最近的并且NUM2是有值的记录
执行SELECT 之后
希望时
2003-01-01 A 5
2003-01-02 B 5
2003-01-03 c 5
2003-01-04 d 31
2003-01-05 e 28
2003-01-06 f 28
2003-01-07 g 28
2003-01-08 j 28
2003-01-09 i 31
2003-01-10 j 31
(select * from t1 where num2 is not null)
union
(select a.rq,a.name,b.num2 from t1 a,t1 b where a.num2 is null
and b.to_char(rq,'yyyymmdd')=(select max(to_char(rq,'yyyymmdd')) from t1 where
rq<a.rq);
(rq date,
name varchar2(20),
num2 number
)select * from y_test
RQ NAME NUM2
1 2003-1-1 A 5
2 2003-1-2 B
3 2003-1-3 c
4 2003-1-4 d 31
5 2003-1-5 e 28
6 2003-1-6 f
7 2003-1-7 g
8 2003-1-8 h
9 2003-1-9 i 31
10 2003-1-10 j select x.rq, m.name, n.num2
from
(select a.rq, max(b.rq) max_rq from
(select * from y_test where num2 is null) a,
(select * from y_test where num2 is not null) b
where a.rq > b.rq
group by a.rq
union
select rq, rq max_rq from y_test where num2 is not null
) x,
y_test m,
y_test n
where x.rq=m.rq
and x.max_rq=n.rq RQ NAME NUM2
1 2003-1-1 A 5
2 2003-1-2 B 5
3 2003-1-3 c 5
4 2003-1-4 d 31
5 2003-1-5 e 28
6 2003-1-6 f 28
7 2003-1-7 g 28
8 2003-1-8 h 28
9 2003-1-9 i 31
10 2003-1-10 j 31
select rq,
name,
nvl(num2, (select num2 from (select * from test order by rq desc) b
where num2 is not null and b.rq<=a.rq and rownum=1)) num22
from test a