Гуру-тест: хитрая задача на SQL (считайте, олимпиадная)
Интересная задачка есть у меня для разминки Ваших мозгов. В моём ЖЖ её никто решить не смог, что странно, давайте среди 1сников устроим челлендж, докажем, что мы умнее сишников. Чтобы веселее было думать, добавляю приз 1 см.
Есть таблица проходов через проходную с полями: Сотрудник, Время (уникальное) , Вход (истина - вход, ложь - выход).
За день могут входить-выходить много раз.
Нужно SQL запросом выдать таблицу: Сотрудник, Время прихода, Время ухода, Секунд внутри. Запись таблицы по каждой последовательной паре приход - уход.
Фишка в том, что нельзя использовать Group By, агрегатные функции Min/Max/..., конструкцию IN. Нужно использовать только один запрос без временных таблиц и пользоваться только JOIN.
Думаем!
Есть таблица проходов через проходную с полями: Сотрудник, Время (уникальное) , Вход (истина - вход, ложь - выход).
За день могут входить-выходить много раз.
Нужно SQL запросом выдать таблицу: Сотрудник, Время прихода, Время ухода, Секунд внутри. Запись таблицы по каждой последовательной паре приход - уход.
Фишка в том, что нельзя использовать Group By, агрегатные функции Min/Max/..., конструкцию IN. Нужно использовать только один запрос без временных таблиц и пользоваться только JOIN.
Думаем!
Найденные решения
Вот, кажется, подходящее решение:
Вариант для проверки в консоли:
ВЫБРАТЬ
Приходы.Сотрудник,
Приходы.Время КАК ВремяПрихода,
Уходы.Время КАК ВремяУхода
ИЗ
Дано КАК Приходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Уходы
ПО Приходы.Сотрудник = Уходы.Сотрудник
И Приходы.Время < Уходы.Время
ЛЕВОЕ СОЕДИНЕНИЕ Дано КАК Вставки
ПО (Вставки.Сотрудник = Приходы.Сотрудник)
И Приходы.Время < Вставки.Время
И (Вставки.Время < Уходы.Время)
ГДЕ
Приходы.Вход
И НЕ Уходы.Вход
И Вставки.Время ЕСТЬ NULL
ПоказатьВариант для проверки в консоли:
ВЫБРАТЬ
"Иванов" КАК Сотрудник,
ДАТАВРЕМЯ(2018, 2, 19, 23, 0, 0) КАК Время,
ИСТИНА КАК Вход
ПОМЕСТИТЬ Дано
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 19, 23, 59, 59),
ЛОЖЬ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 20, 0, 0, 0),
ИСТИНА
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 20, 8, 0, 0),
ЛОЖЬ
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
Приходы.Сотрудник,
Приходы.Время КАК ВремяПрихода,
Уходы.Время КАК ВремяУхода
ИЗ
Дано КАК Приходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Уходы
ПО Приходы.Сотрудник = Уходы.Сотрудник
И Приходы.Время < Уходы.Время
ЛЕВОЕ СОЕДИНЕНИЕ Дано КАК Вставки
ПО (Вставки.Сотрудник = Приходы.Сотрудник)
И Приходы.Время < Вставки.Время
И (Вставки.Время < Уходы.Время)
ГДЕ
Приходы.Вход
И НЕ Уходы.Вход
И Вставки.Время ЕСТЬ NULL
ПоказатьОстальные ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
как-то так?
sel ect
table_in.id as id,
table_in.time as time_in,
table_out.time as time_out,
datediff(ss, table_in.time, table_out.time) AS datedif
fr om timetable as table_in
left join timetable as table_out
on table_out.id = table_in.id
and table_out.state = 0
and table_out.time = (
select top 1
t.time
fr om timetable as t
where t.state = 0
and t.id = table_in.id
and t.time > table_in.time
order by t.time
)
where table_in.state = 1
Показать
Ну или так, вообще без joina
sel ect
t.id,
t.time_in,
t.time_out,
datediff(ss, t.time_in, t.time_out) AS datedif
fr om (
sel ect
table_in.id as id,
table_in.time as time_in,
(select top 1
t.time
fr om timetable as t
where t.state = 0
and t.id = table_in.id
and t.time > table_in.time
order by t.time) as time_out
fr om timetable as table_in
wh ere table_in.state = 1) as t
Показать
Вот, кажется, подходящее решение:
Вариант для проверки в консоли:
ВЫБРАТЬ
Приходы.Сотрудник,
Приходы.Время КАК ВремяПрихода,
Уходы.Время КАК ВремяУхода
ИЗ
Дано КАК Приходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Уходы
ПО Приходы.Сотрудник = Уходы.Сотрудник
И Приходы.Время < Уходы.Время
ЛЕВОЕ СОЕДИНЕНИЕ Дано КАК Вставки
ПО (Вставки.Сотрудник = Приходы.Сотрудник)
И Приходы.Время < Вставки.Время
И (Вставки.Время < Уходы.Время)
ГДЕ
Приходы.Вход
И НЕ Уходы.Вход
И Вставки.Время ЕСТЬ NULL
ПоказатьВариант для проверки в консоли:
ВЫБРАТЬ
"Иванов" КАК Сотрудник,
ДАТАВРЕМЯ(2018, 2, 19, 23, 0, 0) КАК Время,
ИСТИНА КАК Вход
ПОМЕСТИТЬ Дано
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 19, 23, 59, 59),
ЛОЖЬ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 20, 0, 0, 0),
ИСТИНА
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Иванов",
ДАТАВРЕМЯ(2018, 2, 20, 8, 0, 0),
ЛОЖЬ
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
Приходы.Сотрудник,
Приходы.Время КАК ВремяПрихода,
Уходы.Время КАК ВремяУхода
ИЗ
Дано КАК Приходы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Дано КАК Уходы
ПО Приходы.Сотрудник = Уходы.Сотрудник
И Приходы.Время < Уходы.Время
ЛЕВОЕ СОЕДИНЕНИЕ Дано КАК Вставки
ПО (Вставки.Сотрудник = Приходы.Сотрудник)
И Приходы.Время < Вставки.Время
И (Вставки.Время < Уходы.Время)
ГДЕ
Приходы.Вход
И НЕ Уходы.Вход
И Вставки.Время ЕСТЬ NULL
Показать
Поржал сам с себя...
Какой тут NL? Не он страшен. Практически декартово произведение. Запрос вообще не работоспособен.
Взял навскидку табличку на 270к записей... и все... ssd стало жалко )
Чтобы примерить эту Идею - то я ведь правильно все понял?
1. Взяли все до @даты (табл Т1)
2.Присоединили все между периодом (Т1) и @датой - и исключили все что примоединилось
Результат в количестве и процентах на картинке(HM).
На второй картинке (NL) добавлен индекс по Field+Period.
PS: по сабжу - олимпиадные задачи такие олимпиадные. Нет чтобы соревноваться по оптимизации, краткости, интересных подходах. ))
Какой тут NL? Не он страшен. Практически декартово произведение. Запрос вообще не работоспособен.
Взял навскидку табличку на 270к записей... и все... ssd стало жалко )
Чтобы примерить эту Идею - то я ведь правильно все понял?
1. Взяли все до @даты (табл Т1)
2.Присоединили все между периодом (Т1) и @датой - и исключили все что примоединилось
Результат в количестве и процентах на картинке(HM).
На второй картинке (NL) добавлен индекс по Field+Period.
PS: по сабжу - олимпиадные задачи такие олимпиадные. Нет чтобы соревноваться по оптимизации, краткости, интересных подходах. ))
Прикрепленные файлы:
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот