Возьмите ближайшую строку по условию в дате, студенческом кадре
Я изо всех сил пытаюсь получить ближайший «Тест по математике» или «Тест по биологии» через (+/- 3 часа) от Test = «Marked A +», включая заказ TestOrder.
Если «Тест по математике» или «Тест по биологии» были до «Отметки A+» с тем же временем, я получаю максимальный TestOrder для «Отметки A+»
Если «Тест по математике» или «Тест по биологии» были после «Отметка A+» с одинаковым временем, я получаю минимальный 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
Похоже, что желаемые результаты не имеют ничего общего со столбцом TestOrder, но ближе всего зависят от ом [DateTime]
@Stu «ближайший» по отношению к TestOrder означает, что если события с одинаковой датой и временем произошли до того, как мы возьмем событие с наивысшим порядком тестирования
если события с той же датой и временем были после того, как мы взяли событие с наименьшим тестовым заказом (пример студент 2)
Ответы 1
Далее используется apply для сопоставления ближайшей строки по [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+';
См. Демонстрационная рабочий пример
Я добавил комментарий к вашим вопросам
@Aleksandra Александра, я добавила критерии заказа на основе ваших комментариев, соответствует ли это вашим требованиям? Следующий рабочий пример предоставляет ожидаемые результаты.
как реализовать явное соединение для «Отметка A +» на «Тест по математике», «Тест по биологии»? где t.student = t2.student и t2.[datetime] <> t.[datetime] и t.Test = «Отмечен A+» и t2.Test в («Тест по математике», «Тест по биологии»)? Я прав?
and t2.[datetime] <> t.[datetime] позаботится об этом, поскольку эти значения test отличаются от даты «Отметка A+» — однако, если это не работает для ваших реальных данных, вы можете заменить их этими критериями in.
Я спросил об этом, потому что могут быть другие разные тесты, кроме «Теста по математике», «Теста по биологии», и мне нужно присоединиться только к «Отметке A +» на «Тест по математике», «Тест по биологии». Я хотел бы использовать явное соединение там
где у нас есть условие (+/- 3 часа) для «теста по математике», «теста по биологии»?
@Aleksandra Александра, я добавила эти критерии в ответ. Я пропустил 3-часовой критерий, но добавил кое-что, что должно сработать и для этого.
Можете ли вы объяснить, что означает «ближайший» по отношению к столбцу
TestOrder- для Студента 1 две строки имеют одинаковое значение 32, и все студенты имеют один и тот же TestOrder, чем Студент2 отличается от Студент1?