日期关联取最近日期的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

posted @ 2013-07-09 10:18 郭振斌 阅读(...) 评论(...) 编辑 收藏