按日期，学生框架按最近的行

Question

我正在努力从test ='标记为A+'（包括测试订单）的（+/- 3小时）中获得最近的“数学测试”或“生物学测试”。

如果“数学测试”或“生物学测试”是在同一时间标记为A+'之前的 - 我将最大测试仪与“标记为A+”

if“ Math Test”或“ Biology Test”进行了同一时间 - 我将Min Testorder到“标记为A+”

| student | Test         | TestOrder | DateTime                |
| ------- | ------------ | --------- | ----------------------- |
| 1       | Math Test    | 22        | 2022-05-01 19:06:16.207 |
| 1       | Biology Test | 32        | 2022-05-01 19:06:16.207 |
| 1       | Marked A+    | 50        | 2022-05-01 19:06:16.407 |
| 1       | Math Test    | 22        | 2022-05-01 20:06:16.100 |
| 1       | Biology Test | 32        | 2022-05-01 20:06:16.100 |
| 2       | Math Test    | 22        | 2022-05-01 18:06:16.407 |
| 2       | Biology Test | 32        | 2022-05-01 18:06:16.407 |
| 2       | Marked A+    | 50        | 2022-05-01 19:06:16.407 |
| 2       | Math Test    | 22        | 2022-05-01 19:07:16.407 |
| 2       | Biology Test | 32        | 2022-05-01 19:07:16.407 |
| 3       | Math Test    | 22        | 2022-05-01 10:36:12.207 |
| 3       | Biology Test | 32        | 2022-05-01 19:02:16.407 |
| 3       | Marked A+    | 50        | 2022-05-01 19:06:16.407 |
| 3       | Math Test    | 22        | 2022-05-01 20:06:14.002 |
| 3       | Biology Test | 32        | 2022-05-01 21:06:10.107 |
| 4       | Math Test    | 22        | 2022-05-01 17:06:22.101 |
| 4       | Biology Test | 32        | 2022-05-01 18:06:22.101 |
| 4       | Marked A+    | 50        | 2022-05-01 19:06:16.407 |
| 4       | Math Test    | 22        | 2022-05-01 19:06:20.407 |
| 4       | Biology Test | 32        | 2022-05-01 23:06:20.407 |

最终结果已标记为A+“左将连接到最近的活动“数学测试”或“生物学测试”。

| student | Test      | TestOrder | DateTime                | student\_ | Test\_       | TestOrder\_ | DateTime\_              |
| ------- | --------- | --------- | ----------------------- | --------- | ------------ | ----------- | ----------------------- |
| 1       | Marked A+ | 50        | 2022-05-01 19:06:16.407 | 1         | Biology Test | 32          | 2022-05-01 19:06:16.207 |
| 2       | Marked A+ | 50        | 2022-05-01 19:06:16.407 | 2         | Math Test    | 22          | 2022-05-01 19:07:16.407 |
| 3       | Marked A+ | 50        | 2022-05-01 19:06:16.407 | 3         | Biology Test | 32          | 2022-05-01 19:02:16.407 |
| 4       | Marked A+ | 50        | 2022-05-01 19:06:16.407 | 4         | Math Test    | 22          | 2022-05-01 19:06:20.407 |

SELECT t1.student, t1.Test, t1.TestOrder, t1.Datetime
, t2.student_, t2.Test_, t2.TestOrder_, t2.Datetime_
FROM tab1 t1
left join tab1 t2 
    ON t2.Test in ('Math Test', 'Biology Test')          
    AND t2.student = t1.student
    AND DATEADD(HOUR, -3, t1.DateTime) <= t2.DateTime and t2.DateTime < DATEADD(HOUR, 3, t1.DateTime) /* Take from the nearest 'Marked A+' (+/-3 hours) */
WHERE t1.Test = 'Marked A+'

但是我不知道如何根据Testorder列

PS进行最近的“数学测试”，“生物学测试”：我在屏幕截图上使用MSSQL

我突出显示了我的突出显示行

Answer 1

以下使用应用将[DateTime]与源行的最接近行匹配。但是，它并不涉及重复项，您尚未澄清任何要求，但是看看这是否对您有用？

select *
from t
cross apply (
    select top(1) *
    from t t2
    where t.student = t2.student and t2.Test in ('Math Test', 'Biology Test') 
      and Abs(DateDiff(minute,t2.[datetime],t.[datetime]))<= 180
    order by Abs(DateDiff(minute,t2.[datetime],t.[datetime])),
     case when t2.[datetime] > t.[datetime] then TestOrder end ,
     case when t2.[datetime] < t.[datetime] then TestOrder end desc
)m
where t.test='Marked A+';

参见