我有两个表表1和表2
表格1:
-------------------------------
| Ser | StartDate | Activity |
-------------------------------
| 1 | 2002-10-13 | 1 |
| 1 | 2002-10-13 | 2 |
| 1 | 2007-09-04 | 3 |
表2:
------------------------
|Ser|DateOfRegistration|
------------------------
| 1 | 2002-10-12 |
| 1 | 2007-09-02 |
现在,我想要的结果是,对于活动1和活动2,注册日期应早于开始日期,并且日期之间的差异必须最小。同样,对于活动3,活动3的注册日期应早于开始日期。结果应如下所示。
表3:
--------------------------------------------
|Ser|StartDate |DateofRegistration|Activity|
--------------------------------------------
| 1 |2002-10-13| 2002-10-12 | 1 |
| 1 |2002-10-13| 2002-10-12 | 2 |
| 1 |2002-09-04| 2002-09-02 | 3 |
如何加入表1和2以获得表3?
最佳答案
您可以使用outer apply
:
select t1.*, t2.dateofregistration
from table1 t1 outer apply
(select top (1) t2.*
from table2 t2
where t2.ser = t1.ser and t2.dateofregistration < t1.startdate
order by t2.dateofregistration desc
) t2