日期关联取最近日期的SQL
SQL怎么关联,如下图A表用日期加产品编号关联B表的时候,如果日期不存在,则取之前最近一个日期的值,比如A表2012-07-31 关联B表,B表没有对应日期的,就取2012-07-30的
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
成长
/ | \
学习 总结 分享
QQ交流群:122230156