仅使用左联接和ON语句选择最近的值

Question

关于如何使用LEFTJOIN来完成这个任务，我有个问题。是否有一种方法可以让LEFT JOIN和ON在查询中只选择最近的值，而查询中的值是LEFT JOIN？

例如，我从下面的查询中获得了基于校验日期排序的值，并根据2019-12-25对值进行了排序。我只想选择与2019-12-25 14:50:00.000最接近的值，因此，从技术上讲，应该使用的唯一值是2019-12-25 14:51:57.000，因为它是最近的，我们如何使用LEFT JOIN来实现这一点？

+-------------+-----------+-------------------------+------------+
| badgenumber | checktype | recordout               | checkdate  |
+-------------+-----------+-------------------------+------------+
| 1233        | O         | 2019-12-25 14:00:02.000 | 2019-12-25 | 
+-------------+-----------+-------------------------+------------+
| 1233        | O         | 2019-12-25 14:39:57.000 | 2019-12-25 |
+-------------+-----------+-------------------------+------------+
| 1233        | O         | 2019-12-25 14:51:57.000 | 2019-12-25 |
+-------------+-----------+-------------------------+------------+
| 1233        | O         | 2019-12-25 22:41:57.000 | 2019-12-25 |
+-------------+-----------+-------------------------+------------+
| 1233        | O         | 2019-12-25 22:52:57.000 | 2019-12-25 |
+-------------+-----------+-------------------------+------------+

这是我正在处理的查询，employeeidno, fullname, departmentname是隐藏的。

使用的值如下。

• t0.noofhoursduty =8
• t0.mergetimeoutorig =2019年-12-25 14:50:00.000

这是包含LEFT JOIN的查询。

LEFT JOIN

(SELECT 
    MAX(userinfo.badgenumber) AS badgenumber, 
    MAX(RTRIM(checkinout.checktype)) AS 'checktype', 
    MAX(checkinout.checktime) as 'recordout', 
    MAX(CONVERT(date,checkinout.checktime)) as checkdate, 
    MAX(RTRIM(employeemasterfile.employeeidno)) AS 'employeeidno', 
    MAX(RTRIM(employeemasterfile.lastname))+', '+ 
    MAX(RTRIM(employeemasterfile.firstname))+' '+ MAX(LEFT(employeemasterfile.middlename,1))+'.' AS 'fullname', 
    MAX(RTRIM(departmentmasterfile.departmentname)) AS 'departmentname' 
FROM ((checkinout INNER JOIN userinfo 
    ON checkinout.userid = userinfo.userid) 
INNER JOIN employeemasterfile 
    ON userinfo.badgenumber = employeemasterfile.fingerscanno) 
INNER JOIN departmentmasterfile 
    ON LEFT(employeemasterfile.employeeidno, 4) = LEFT(departmentmasterfile.departmentcode, 4) 
WHERE 
CONVERT(date,checkinout.checktime) BETWEEN '2019-12-21' AND DATEADD(DAY, 1,'2020-01-05') 
    AND CHECKINOUT.CHECKTYPE = 'O' COLLATE SQL_Latin1_General_CP1_CS_AS 
GROUP BY 
    userinfo.badgenumber, LEFT(checkinout.checktime,14)) AS t2
ON 
    t2.recordout BETWEEN DATEADD(HOUR,-(t0.noofhoursduty/2),t0.mergetimeoutorig) AND DATEADD(HOUR, 1,t0.mergetimeoutorig)
    AND t2.badgenumber = t0.fingerscanno
    AND t0.schedulename !='REST'

这将是上述查询的输出。相同的日期结果是可以的，因为有一个人在一天中以不同的时间戳登录4倍的实例。或者一个人在一天之内有两个或更多的时间表。

    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | badgenumber | checktype | recordout               | checkdate  | employeeidno | fullname | departmentname |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-21 23:43:36.000 | 2019-12-21 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-22 22:36:50.000 | 2019-12-22 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-23 18:03:16.000 | 2019-12-23 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-24 22:06:58.000 | 2019-12-24 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-25 14:00:02.000 | 2019-12-25 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-25 14:39:57.000 | 2019-12-25 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-25 14:51:57.000 | 2019-12-25 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-25 22:41:57.000 | 2019-12-25 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-25 22:52:57.000 | 2019-12-25 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-26 14:00:02.000 | 2019-12-26 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-28 22:00:01.000 | 2019-12-28 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-28 23:31:11.000 | 2019-12-28 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-29 15:08:10.000 | 2019-12-29 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2019-12-30 16:03:20.000 | 2019-12-30 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2020-01-02 06:52:18.000 | 2020-01-02 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2020-01-03 08:00:57.000 | 2020-01-03 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+
    | 1233        | O         | 2020-01-04 06:40:11.000 | 2020-01-04 | ------------ | -------- | -------------- |
    +-------------+-----------+-------------------------+------------+--------------+----------+----------------+

我现在得到的信息似乎是有效的，但是当时间戳有很大的差异时，数据就会被复制，例如，如果person时间戳'O‘(校验类型)在12-9 at 附近，然后在10-7 7PM附近再次按下，记录将显示同一日期内的2个数据，这是好的，我的主要问题是如何选择与LEFT JOIN中最接近的值。

总之，如果我打算使用LEFT JOIN 2019-12-25 14:50:00.000作为左联接的基础，那么只有2019-12-25 14:51:57.000才能选择2019-12.25 14:51:57.000。我尝试使用BETWEEN和DATEADD(-+HOUR)，但它仍在选择同一记录中的所有数据。有办法这样做吗？或者这是合理的，只使用LEFT JOIN

有没有办法修改这些代码以达到我的目标？

ON 
    t2.recordout BETWEEN DATEADD(HOUR,-(t0.noofhoursduty/2),t0.mergetimeoutorig) AND DATEADD(HOUR, 1,t0.mergetimeoutorig)
    AND t2.badgenumber = t0.fingerscanno
    AND t0.schedulename !='REST'

希望有一个积极的反应，这已经困扰了我很长一段时间，如何使用左连接。

Answer 1

使用外部APPLY而不是左联接，只使用适当的order选择第一个匹配行，如下所示：

...
OUTER APPLY (
 select top(1) *
 from (
        SELECT 
        MAX(userinfo.badgenumber) AS badgenumber, 
        -- you current t2 subquery
      ) AS t2
 where 
    t2.recordout BETWEEN DATEADD(HOUR,-(t0.noofhoursduty/2),t0.mergetimeoutorig) AND DATEADD(HOUR, 1,t0.mergetimeoutorig)
    AND t2.badgenumber = t0.fingerscanno
    AND t0.schedulename !='REST'
 order by abs(datediff(minute, t0.mergetimeoutorig, t2.recordout )) desc
) t2

这很难进行查询，因为我没有看到任何示例数据和表结构，但希望它有助于达到正确的方向。