表1
RQ Name
2007-1-1 aa
2007-1-2 bb
2007-1-3 cc
2007-1-4 dd
2007-1-5 ee
2007-1-6 ff表2
RQ Numb
2007-1-3 10
2007-1-6 12查询得到:
2007-1-1 aa 10
2007-1-2 bb 10
2007-1-3 cc 10
2007-1-4 dd 12
2007-1-5 ee 12
2007-1-6 ff 12也就是以RQ做连接,两个表连接查询
RQ Name
2007-1-1 aa
2007-1-2 bb
2007-1-3 cc
2007-1-4 dd
2007-1-5 ee
2007-1-6 ff表2
RQ Numb
2007-1-3 10
2007-1-6 12查询得到:
2007-1-1 aa 10
2007-1-2 bb 10
2007-1-3 cc 10
2007-1-4 dd 12
2007-1-5 ee 12
2007-1-6 ff 12也就是以RQ做连接,两个表连接查询
insert into t5
select to_date('2007-1-1','yyyy-mm-dd'),'aa' from dual union all
select to_date('2007-1-2','yyyy-mm-dd'),'bb' from dual union all
select to_date('2007-1-3','yyyy-mm-dd'),'cc' from dual union all
select to_date('2007-1-4','yyyy-mm-dd'),'dd' from dual union all
select to_date('2007-1-5','yyyy-mm-dd'),'ee' from dual union all
select to_date('2007-1-6','yyyy-mm-dd'),'ff' from dual;
/
create table t6(rQ date, Numb int);
insert into t6
select to_date('2007-1-3','yyyy-mm-dd'),10 from dual union all
select to_date('2007-1-6','yyyy-mm-dd'),12 from dual;
--创建函数
create or replace function getNumb( arq in date ) return int
is
rst int;
begin
select numb into rst from t6 where arq<=rq and rownum=1;
return rst;
exception
when others then
rst:=0;
end;
--执行查询
select a.*,getNumb(a.rq) numb
from t5 a--输出结果
2007-1-1 aa 10
2007-1-2 bb 10
2007-1-3 cc 10
2007-1-4 dd 12
2007-1-5 ee 12
2007-1-6 ff 12
FROM (SELECT a.*, b.numb
FROM 表1 a, 表2 b
WHERE a.rq <= b.rq) d
GROUP BY rq, NAME
这样不就ok了吗?
用一条SQL语句能实现吗?
不求numb的最小值,求 表2 rq 的最小值,谢谢