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