Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
 Re: Запрос с интересной выборкой  [new]
S_A_V_e
Member

Откуда:
Сообщений: 176
Попробуйте вот такой вариант:
declare @carData table(Id int,FlightNumber int, OperationTime datetime, BorderRoutingID int)

insert into @carData
select 1,1,'2018-01-01',1
union all
select 2,1,'2018-01-02',1
union all
select 3,1,'2018-01-03',2
union all
select 4,1,'2018-01-04',1
union all
select 5,2,'2018-01-01',1
union all
select 6,2,'2018-01-02',2
union all
select 7,2,'2018-01-03',1
union all
select 8,2,'2018-01-04',2

;with
outs as(select * from @carData where BorderRoutingID = 1  ), -- только выезды
ins as( select * from @carData where BorderRoutingID = 2 ), -- только въезды
inOutData as( select 
			   o.*,
			   ( select min(oi.OperationTime) -- ближайший выезд (более поздний по времени)
				 from outs oi 
				 where 
				 oi.FlightNumber = o.FlightNumber
				 and
				 oi.OperationTime > o.OperationTime) as nearestOut,
			   ( select min(i.OperationTime) -- ближайший въезд (более поздний по времени)
				 from ins i
				 where 
				 i.FlightNumber = o.FlightNumber
				 and
				 i.OperationTime > o.OperationTime) as nearestIn
			   from outs o -- проход по всем выездам      
			   ),
result as( select 
           * 
           from inOutData 
		   where 
		   nearestOut < nearestIn -- берём записи где выезд раньше въезда
		   )
select * from result
29 янв 18, 14:25    [21147305]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
invm
Member

Откуда: Москва
Сообщений: 8712
k.I.titov,

Ответы не пробовали читать?
+
declare @carData table(Id int, FlightNumber int, OperationTime datetime, BorderRoutingID int);

insert into @carData
select 1,1,'20180101',1
union all
select 2,1,'20180102',1
union all
select 3,1,'20180103',2
union all
select 4,1,'20180104',1
union all
select 5,2,'20180101',1
union all
select 6,2,'20180102',2
union all
select 7,2,'20180103',1
union all
select 8,2,'20180104',2;

with t as
(
 select
  FlightNumber, OperationTime, BorderRoutingID,
  lag(BorderRoutingID) over (partition by FlightNumber order by OperationTime) as PrevBorderRoutingID
 from
  @carData
)
select distinct
 FlightNumber
from
 t
where
 BorderRoutingID = 1 and
 PrevBorderRoutingID = 1;
29 янв 18, 14:43    [21147376]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28867
k.I.titov
petre,

Запустил, результат есть, но проверка показала что номера которые есть в результате, то есть машины въезжали между выездами...
Дык покажите запрос то.
Я вам дал правильный готовый запрос сразу же, а у вас там появляются какие то "t1.BorderCrossingID =1 and t2.BorderCrossingID =2"
k.I.titov
Я мог бы с радостью это сделать, но с на это можно. Попробую так объяснить. Грубо говоря в таблице три записи все трис номером А 111 А 123, время у первой записи 2018.01.01 10:00:00.000, у второй 2018.01.01 11:00:00.000, у третьей 2018.01.01 12:00:00.00, направление соответственно выезд, въезд и выезд то, есть 1,2,1. Запустив этот запрос он увидит что номер А 111 А 123 выехал два раза и выдаст его в результате, не учитывая того, что между ними был въезд. А мне нужно так, что бы он выдал его только при таком раскладе, если у них будет последовательность направлений 1,1,2 соответственно выезд, выезд и въезд
Да поняли все задачу.
Мой запрос как раз учитывает, что между двумя выездами не должно быть въездов.
Вы очевидно что то напутали в запросе.
Покажите же его.
29 янв 18, 15:11    [21147495]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, искал похожую тему и наткнулся на Ваш запрос. Возник вопрос - в какой части Вашего запроса исключается въезд между выездами? Насколько я понимаю оператор cross apply выполняет соединение двух таблиц, при этом каждая строка из левой таблицы сочетается с каждой строкой из правой. Если не трудно, поясните, пожалуйста.
7 фев 19, 19:03    [21803807]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28867
masql60
alexeyvg, искал похожую тему и наткнулся на Ваш запрос. Возник вопрос - в какой части Вашего запроса исключается въезд между выездами? Насколько я понимаю оператор cross apply выполняет соединение двух таблиц, при этом каждая строка из левой таблицы сочетается с каждой строкой из правой. Если не трудно, поясните, пожалуйста.
Ну, тут лучше не говорить в терминах "соединение", "левой", "правой". Я от этой математики сразу тупею :-)

Рассмотрим запрос:
select *
from [Есть таблица] as t1
    cross apply (
        select top 1 *
        from [Есть таблица] as t2
        where t2.FlightNumber = t1.FlightNumber
                and t2.OperationTime > t1.OperationTime
        order by t2.OperationTime ASC
    ) as t2
where t1.BorderRoutingID = Выехали
Логически (не будем вдаваться, как его выполняет сервер), он для каждой записи, у которой тип "Выехали", ищет ближайшую к ней (по OperationTime) запись, причём с любыми типами BorderRoutingID. И просто выводит список.

А потом мы, добавляя условие "t2.BorderRoutingID = Выехали", оставляем из результата только те пары записей, в которых вторая запись будет типа Выехали

Соответсвенно, въезд между выездами исключается как раз вот этим дополнительным условием.

Вот если бы условие "t2.BorderRoutingID = Выехали" я поставил внутри cross apply, то я просто нашёл бы для каждой строки ближайшую к ней запись "Выехали", независимо от того, если ли какие то другие записи между ними, или нет.
А вот когда я фильтрую снаружи, то уже фильтрую общий результат.

Единственно, замечу, что для реального применения нужно ещё внутрь добавить условие "t2.BorderRoutingID in ( Выехали, Въехали )", потому что там, наверное, будут другие операции, которые будут искажать результат.

Потому что, по сути, мой изначальный запрос не как у автора "Нужно вытащить все номера авто, которые два и более раз подряд выехали и между этими выездами не было въезда.", а "все номера авто, для которых после выезда сразу следует ещё один выезд", а это не одно и то же.

Итого, получается так:
select *
from [Есть таблица] as t1
    cross apply (
        select top 1 *
        from [Есть таблица] as t2
        where t2.FlightNumber = t1.FlightNumber
                and t2.OperationTime > t1.OperationTime
                and t2.BorderRoutingID in ( Выехали, Въехали )
        order by t2.OperationTime ASC
    ) as t2
where t1.BorderRoutingID = Выехали
    and t2.BorderRoutingID = Выехали
7 фев 19, 19:37    [21803820]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, спасибо за пояснение. Я не учел то, что условие задаете вне оператора cross apply. Обрисую свою ситуацию. Есть две таблицы personal и operations. Таблица personal состоит из столбцов Id и FIO, таблица operations - Id, OperationName, OperationDate, FIOId. Из таблицы personal я вытягиваю FIO, из таблицы operations - OperationDate, OperationName, использую оператор left join при объединении. Так вот мне требуется выбрать только те записи, которые подпадают под следующее условие - сотрудник дважды вышел, но не входил. Приведу пример:
Иванов Иван Иванович Вход 01.01.2017 10:00
Петров Петр Петрович Вход 01.01.2017 10:05
Иванов Иван Иванович Выход 01.01.2017 13:00
Петров Петр Петрович Выход 01.01.2017 13:05
Петров Петр Петрович Вход 01.01.2017 14:00
Иванов Иван Иванович Выход 01.01.2017 18:00
Петров Петр Петрович Выход 01.01.2017 18:05
На выходе я хочу получить:
Иванов Иван Иванович Выход 01.01.2017 13:00
Иванов Иван Иванович Выход 01.01.2017 18:00
Использую Ваш запрос. Вот что получилось:
SELECT p.FIO, o.OperationName, o.OperationDate
FROM personal AS p LEFT JOIN operations AS o ON p.Id = o.FIOId
CROSS APPLY (SELECT TOP 1 p2.FIO, o2.OperationName, o2.OperationDate FROM personal AS p2 LEFT JOIN operations AS o2 ON p2.Id = o2.FIOId WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId
ORDER BY o2.OperationDate ASC) AS t2
WHERE o.OperationName = 'Выход' AND t2.OperationName = 'Выход' AND o.OperatinDate BETWEEN '01.01.2017 00:00' AND '01.01.2017 23:59'
Получаю все записи с выходом. Помогите, пожалуста, разобраться.
8 фев 19, 09:30    [21804026]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
zby
Member

Откуда:
Сообщений: 33
masql60,

Пожалуйста, форматируйте текст хоть как-нибудь,

а то прям аж кровь из глаз ))
8 фев 19, 09:56    [21804059]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
zby, я извиняюсь за неудобство. Пишу с телефона, не судите строго.
8 фев 19, 10:03    [21804067]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28867
masql60
Помогите, пожалуста, разобраться.
ИМХО вы напутали с Id и FIOId

И вообще, запрос нужно упростить, вместо первого LEFT JOIN достаточно JOIN, а в CROSS APPLY вообще достаточно одной таблицы.
SELECT p.FIO, o.OperationName, o.OperationDate
FROM personal AS p 
	JOIN operations AS o 
		ON p.Id = o.FIOId
	CROSS APPLY (
		SELECT TOP 1 o2.OperationName
		FROM operations AS o2 
		WHERE o2.OperationDate > o.OperatinDate 
			AND o2.FIOId = o.FIOId 
		ORDER BY o2.OperationDate ASC
	) AS t2 
WHERE o.OperationName = 'Выход' 
	AND t2.OperationName = 'Выход' 
	AND o.OperatinDate BETWEEN '01.01.2017 00:00' AND '01.01.2017 23:59'
8 фев 19, 23:18    [21804981]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, согласен с Вами, что объединение во встроенном запросе лишнее. Но что я напутал? Я так и не заметил разницы, помимо того, что Вы исключили одно объединение.
9 фев 19, 18:11    [21805343]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
Если вы имеете это - WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId, то это моя опечатка с телефона.
9 фев 19, 18:15    [21805344]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 28867
masql60
Если вы имеете это - WHERE o2.OperationDate > o.OperatinDate AND p2.FIOId = p.FIOId, то это моя опечатка с телефона.
Да, имею в виду это.

Ну, что можно сказать? Когда будете у компа, запостьте сюда скрипт создания ваших таблиц, с вашими тестовыми данными "Иванов Иван Иванович Вход 01.01.2017 10:00" и т.п., отладим ваш запрос. Благо скрипт можно получить в SSMS, а тестовые данные вы уже тут запостили, так что это несложно.

А то получается "лечение по пересказу", мало ли, что вы в телефоне набрали, может, там ещё есть какое то отличие в запросе, достаточно ведь не там скобку поставить.
9 фев 19, 23:33    [21805471]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, да, я прекрасно Вас понимаю. Тогда я в понедельник создам тестовую базу, и отправлю результат.
10 фев 19, 12:04    [21805593]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с интересной выборкой  [new]
masql60
Member

Откуда:
Сообщений: 7
alexeyvg, учел все Ваши замечания, и получилось. Спасибо большое за помощь!
12 фев 19, 14:09    [21807396]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2]      все
Все форумы / Microsoft SQL Server Ответить