sql - 从多个 ID 中获取最接近的日期

Question

我需要将 @sampleTrans 中的 idno 记录与 @sampleReceipt 中最接近的日期 (rodt) 进行匹配。我有查询，但它给了我不同的结果。很感谢任何形式的帮助。请参阅下面的示例 DDL 和预期结果。谢谢。

DEclare @sampleReceipt table
(
csno varchar(50), idno varchar(50), rdt datetime, descript varchar(35))

insert @sampleReceipt values ('ABCDE5B12AG123383814.0611','ABCDE5B12AG12338','2018-04-18 07:34:34.000','receive')
insert @sampleReceipt values ('ABCDE5B12AG123383064.3556','ABCDE5B12AG12338','2018-01-02 10:59:11.000','receive')
insert @sampleReceipt values ('ABCDE5B12AG123383064.3559','ABCDE5B12AG12338','2018-08-14 10:59:11.000','receive')

insert @sampleReceipt values ('ABCDE5B12AG333352814.2771','ABCDE5B12AG33335','2018-06-02 02:56:07.000','receive')
insert @sampleReceipt values ('12345678901234612813.6785','1234567890123461','2018-05-16 18:55:11.000','receive')
insert @sampleReceipt values ('11111118901234612813.6785','1111111890123461','2018-07-16 19:55:11.000','receive')
insert @sampleReceipt values ('22222228901234612813.6785','2222222890123461','2018-07-19 06:59:10.000','receive')

Declare  @sampleTrans table
(id varchar(50), idno nvarchar (50), recover varchar(50),  dt datetime, roid varchar(35), linestatus int, lineid varchar(35), opdesc varchar(35) ,descript varchar(35))

insert @sampleTrans values ('SAL00CABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-02-15 10:48:03.000','SAL00C',1,'H_LineD','created','created')
insert @sampleTrans values ('SAL00CABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-03-20 23:20:03.000','SAL00C',1,'H_LineD','complete','good')
insert @sampleTrans values ('SAL00AABCDE5B12AG12338','ABCDE5B12AG12338',''     ,'2018-06-15 23:48:03.000','SAL00A',1,'H_Dis','complete','good')
insert @sampleTrans values ('SAL00AABCDE5B12AG12338','ABCDE5B12AG12338',''     ,'2018-06-13 14:59:24.000','SAL00A',1,'H_Dis','created','created')
insert @sampleTrans values ('SAL00BABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-06-30 15:02:25.000','SAL00B',1,'H_kt','created','created')
insert @sampleTrans values ('SAL00BABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-07-05 13:20:41.000','SAL00B',1,'H_kt','complete','good')
insert @sampleTrans values ('BOI002ABCDE5B12AG33335','ABCDE5B12AG33335','80539','2018-07-10 10:33:01.000','BOI002',3,'H_Dis','complete','bad')
insert @sampleTrans values ('BOI002ABCDE5B12AG33335','ABCDE5B12AG33335','80539','2018-06-26 08:42:01.000','BOI002',3,'H_Dis','created','created')
insert @sampleTrans values ('BAL0451234567890123461','1234567890123461','','2018-06-13 21:37:33.000','BAL045',1,'H_Dis','created','created')
insert @sampleTrans values ('BAL0451234567890123461','1234567890123461','','2018-06-15 23:43:01.000','BAL045',1,'H_Dis','complete','good')
insert @sampleTrans values ('BAL0561234567890123461','1234567890123461','70418','2018-07-05 12:25:31.000','BAL056',1,'H_kt','complete','good')
insert @sampleTrans values ('BAL0561234567890123461','1234567890123461','70418','2018-06-30 15:03:28.000','BAL056',1,'H_kt','created','created')
insert @sampleTrans values ('BAL0591111111890123461','1111111890123461','70419','2018-07-25 15:03:28.000','BAL059',1,'H_Line','created','created')



select t1.id, t1.idno, t1.recover, t1.dt, t1.roid, t1.opdesc, t1.lineid , t2.csno, t2.rdt
from @sampleTrans t1
left join @sampleReceipt t2
on t1.idno= t2.idno
and t1.dt >= t2.rdt
order by t1.idno, t1.dt


Expected Result

id----------------------idno--------------recover----dt-----------------------roid----opdesc---lineid----rodt---------------------csno
BAL0591111111890123461--1111111890123461--70419------2018-07-25 15:03:28.000--BAL059--created-- H_Line---2018-07-16 19:55:11.000--BAL0591111111890123461
BAL0451234567890123461--1234567890123461--NULL-------2018-06-13 21:37:33.000--BAL045--created-- H_Dis----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0451234567890123461--1234567890123461--NULL-------2018-06-15 23:43:01.000--BAL045--complete--H_Dis----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0561234567890123461--1234567890123461--70418------2018-06-30 15:03:28.000--BAL056--created-- H_kt-----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0561234567890123461--1234567890123461--70418------2018-07-05 12:25:31.000--BAL056--complete--H_kt-----2018-05-16 18:55:11.000--BAL0561234567890123461
SAL00CABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-02-15 10:48:03.000--SAL00C--created-- H_LineD--2018-01-02 10:59:11.000--ABCDE5B12AG123383814.0611
SAL00CABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-03-20 23:20:03.000--SAL00C--complete--H_LineD--2018-01-02 10:59:11.000--ABCDE5B12AG123383814.0611
SAL00AABCDE5B12AG12338--ABCDE5B12AG12338--NULL-------2018-06-13 14:59:24.000--SAL00A--created-- H_Dis----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00AABCDE5B12AG12338--ABCDE5B12AG12338--NULL-------2018-06-15 23:48:03.000--SAL00A--complete--H_Dis----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00BABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-06-30 15:02:25.000--SAL00B--created-- H_kt-----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00BABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-07-05 13:20:41.000--SAL00B--complete--H_kt-----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
BOI002ABCDE5B12AG33335--ABCDE5B12AG33335--80539------2018-06-26 08:42:01.000--BOI002--created-- H_Dis----2018-06-02 02:56:07.000--ABCDE5B12AG333352814.2771
BOI002ABCDE5B12AG33335--ABCDE5B12AG33335--80539------2018-07-10 10:33:01.000--BOI002--complete--H_Dis----2018-06-02 02:56:07.000--ABCDE5B12AG333352814.2771

Answer 1

您可以使用OUTER APPLY仅获取最接近的记录 - 每行仅一条记录，如下所示:

select t1.id, t1.idno, t1.recover, t1.dt, t1.roid, t1.opdesc, t1.lineid , s.csno, s.rdt
from @sampleTrans t1
OUTER APPLY 
(
    SELECT TOP 1 *
    FROM @sampleReceipt t2
    WHERE  t1.idno= t2.idno
        and t1.dt >= t2.rdt
    order by t2.rdt

) S
ORDER BY t1.idno, t1.dt