日期关联取最近日期的SQL

SQL怎么关联，如下图A表用日期加产品编号关联B表的时候，如果日期不存在，则取之前最近一个日期的值，比如A表2012-07-31 关联B表，B表没有对应日期的，就取2012-07-30的

A表			B表
日期	产品编号	值1	日期	产品编号	值1
			2012-7-30	F003003	11
2012-7-31	F003003	1
2012-8-1	F003003	2	2012-8-1	F003003	12
2012-8-2	F003003	3	2012-8-2	F003003	13
2012-8-3	F003003	4	2012-8-3	F003003	14
2012-8-4	F003003	5
2012-8-5	F003003	6
2012-8-6	F003003	7	2012-8-6	F003003	15
2012-8-7	F003003	8	2012-8-7	F003003	16
2012-8-8	F003003	9	2012-8-8	F003003	17
2012-8-9	F003003	10	2012-8-9	F003004	18


结果
日期	产品编号	值1			值2
2012-7-31	F003003	1	2012-7-30	F003003	11
2012-8-1	F003003	2	2012-8-1	F003003	12
2012-8-2	F003003	3	2012-8-2	F003003	13
2012-8-3	F003003	4	2012-8-3	F003003	14
2012-8-4	F003003	5	2012-8-3	F003003	14
2012-8-5	F003003	6	2012-8-3	F003003	14
2012-8-6	F003003	7	2012-8-6	F003003	15
2012-8-7	F003003	8	2012-8-7	F003003	16
2012-8-8	F003003	9	2012-8-8	F003003	17
2012-8-9	F003003	10	2012-8-9	F003004	18

SELECT * FROM (
SELECT a.done_date,a.done_code,a.ext,
last_value(b.done_date IGNORE NULLS) over(ORDER BY nvl(b.done_date,a.done_date)) bdone_date,
last_value(b.done_code IGNORE NULLS) over(ORDER BY nvl(b.done_date,a.done_date)) bdonecode,
last_value(b.ext IGNORE NULLS) over(ORDER BY nvl(b.done_date,a.done_date)) bext
FROM a FULL JOIN b
ON a.done_date=b.done_date
AND a.done_code=b.done_code
) WHERE done_date IS NOT NULL
/

DONE_DATE DONE_CODE       EXT BDONE_DATE BDONECODE    BEXT
----------- ---------- ---------- ----------- ---------- ----------
2012-07-31 F003003       1 2012-07-30 F003003       11
2012-08-01 F003003       2 2012-08-01 F003003       12
2012-08-02 F003003       3 2012-08-02 F003003       13
2012-08-03 F003003       4 2012-08-03 F003003       14
2012-08-04 F003003       5 2012-08-03 F003003       14
2012-08-05 F003003       6 2012-08-03 F003003       14
2012-08-06 F003003       7 2012-08-06 F003003       15
2012-08-07 F003003       8 2012-08-07 F003003       16
2012-08-08 F003003       9 2012-08-08 F003003       17
2012-08-09 F003003       10 2012-08-09 F003003       18

10 rows selected