#sql #sql-server #tsql #sql-server-2014
Вопрос:
У меня есть таблица с именем tblEventRegister(Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50)) .
В таблице приведены следующие данные:
EventDate TagName EventName TagValue
2021-03-18 12:06:35.547 A1 E1 1
2021-03-18 12:06:37.547 A2 E1 1
2021-03-18 12:06:38.547 A3 E1 1
2021-03-18 12:06:45.547 A1 E1 0
2021-03-18 12:06:47.547 A1 E1 0
2021-03-18 12:06:48.547 A1 E1 1
2021-03-18 12:06:55.547 A1 E1 0
2021-03-18 12:06:56.547 A1 E1 0
2021-03-18 12:06:57.547 A1 E1 1
Если значение TagValue=1, то дата события-это время ожидания, а если значение tagvalue равно 0, то дата события-время ожидания. Я использовал следующий запрос, чтобы найти время входа и выхода.
select EventDate, TagName, EventName, Tagvalue
, (case Tagvalue when 1 then EventDate end) intime
, (case Tagvalue when 0 then EventDate end) outtime
from tblEventRegister with (nolock)
В результате получается
EventDate TagName EventName TagValue InTime OutTime
2021-03-18 12:06:35.547 A1 E1 1 2021-03-18 12:06:35.547 Null
2021-03-18 12:06:37.547 A2 E1 1 2021-03-18 12:06:37.547 Null
2021-03-18 12:06:38.547 A3 E1 1 2021-03-18 12:06:38.547 Null
2021-03-18 12:06:45.547 A1 E1 0 Null 2021-03-18 12:06:45.547
2021-03-18 12:06:47.547 A2 E1 0 Null 2021-03-18 12:06:47.547
2021-03-18 12:06:48.547 A1 E1 1 2021-03-18 12:06:48.547 Null
2021-03-18 12:06:55.547 A3 E1 0 Null 2021-03-18 12:06:55.547
2021-03-18 12:06:56.547 A1 E1 0 Null 2021-03-18 12:06:56.547
2021-03-18 12:06:57.547 A1 E1 1 2021-03-18 12:06:56.547 Null
Но результат должен быть
EventDate TagName EventName TagValue InTime OutTime
2021-03-18 12:06:35.547 A1 E1 1 2021-03-18 12:06:35.547 2021-03-18 12:06:45.547
2021-03-18 12:06:37.547 A2 E1 1 2021-03-18 12:06:37.547 2021-03-18 12:06:47.547
2021-03-18 12:06:38.547 A3 E1 1 2021-03-18 12:06:38.547 2021-03-18 12:06:55.547
2021-03-18 12:06:48.547 A1 E1 1 2021-03-18 12:06:48.547 2021-03-18 12:06:56.547
2021-03-18 12:06:57.547 A1 E1 1 2021-03-18 12:06:57.547 Null
Как я могу этого достичь?
Комментарии:
1. @DaleK я уже предоставил образцы данных с определением таблицы и ее столбцов, сэр
2. Какая логика определяет, что строка с
EventDate2021-03-18 12:06:45.547 иTagValue0 соответствует строке сEventDate2021-03-18 12:06:35.547 иTagValue1 ? Существует несколько строк с одинаковымиTagNameEventNameзначениями и, поэтому нет возможности объединить их, если вы не опустили некоторую информацию3. @SunnySandeep Я отмечаю, что у вас много неприемлемых ответов — я призываю вас пересмотреть их и принять некоторые.
Ответ №1:
Следующее возвращает желаемые результаты… обратите внимание, однако, что оно не учитывает никаких других возможных проблем с данными. Таким образом, предполагается, что для каждого времени будет совпадать время выхода или нет. Но он не будет обрабатывать несколько раз во время или вне времени.
Как это работает, выделяя номер строки для каждой пары tagName, TagValue, а затем соединяет их.
declare @tblEventRegister table (Tagvalue int, EventDate datetime, TagName nvarchar(50), EventName nvarchar(50));
insert into @tblEventRegister (EventDate, TagName, EventName, TagValue)
values
('2021-03-18 12:06:35.547', 'A1', 'E1', 1),
('2021-03-18 12:06:37.547', 'A2', 'E1', 1),
('2021-03-18 12:06:38.547', 'A3', 'E1', 1),
('2021-03-18 12:06:45.547', 'A1', 'E1', 0),
('2021-03-18 12:06:47.547', 'A2', 'E1', 0),
('2021-03-18 12:06:48.547', 'A1', 'E1', 1),
('2021-03-18 12:06:55.547', 'A3', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 0),
('2021-03-18 12:06:56.547', 'A1', 'E1', 1);
with cte as (
select *
, row_number() over (partition by TagName, TagValue order by EventDate) rn
from @tblEventRegister
)
select C1.EventDate, C1.TagName, C1.EventName, C1.TagValue, C1.Eventdate, C2.EventDate
from cte C1
left join cte C2 on C2.TagName = C1.TagName and C2.TagValue = 0 and C1.rn = C2.rn
where C1.TagValue = 1
order by C1.EventDate asc;
ВОЗВРАТ:
| Дата события | ТагНаме | Имя события | Значение тега | Дата события | Дата события |
|---|---|---|---|---|---|
| 2021-03-18 12:06:35.547 | A1 | E1 | 1 | 2021-03-18 12:06:35.547 | 2021-03-18 12:06:45.547 |
| 2021-03-18 12:06:37.547 | A2 | E1 | 1 | 2021-03-18 12:06:37.547 | 2021-03-18 12:06:47.547 |
| 2021-03-18 12:06:38.547 | A3 | E1 | 1 | 2021-03-18 12:06:38.547 | 2021-03-18 12:06:55.547 |
| 2021-03-18 12:06:48.547 | A1 | E1 | 1 | 2021-03-18 12:06:48.547 | 2021-03-18 12:06:56.547 |
| 2021-03-18 12:06:56.547 | A1 | E1 | 1 | 2021-03-18 12:06:56.547 | нулевой |
Примечание 1: Здесь показано, как представить ваши примеры данных в формате DDL DML — я рекомендую вам включить это в будущем для более быстрого ответа.
Примечание 2: Не используйте with (nolock) в качестве параметра запроса по умолчанию, так как результаты могут быть неожиданными. Вы должны использовать эту подсказку только в том случае, если вы полностью понимаете последствия, и вы абсолютно обязаны это сделать.
Комментарии:
1. Извините за поздний ответ, Ваш ответ абсолютно хорош. Только один вопрос, если одно и то же значение повторяется, например, 1 повторяется после 1, то последнее 1 должно отображаться не повторно. как мы можем справиться. это?
2. @SunnySandeep извините, я не понимаю… Я думаю, что лучше всего задать новый вопрос.
Ответ №2:
Если я предполагаю, что за каждым «вход» следует не более одного соответствующего «выход», то есть нет «входов», за которыми следует «вход» или «выход», за которыми следует «выход», тогда эта проблема не является сложной. С более грязными данными это становится намного сложнее.
Один из подходов состоит в том, чтобы просто перечислить строки по «входным»и «выходным», а затем агрегировать:
select tagname, eventname, max(tagvalue), min(eventdate) as intime,
nullif(max(eventdate), min(eventdate)) as outtime
from (select er.*,
row_number() over (partition by tagname, eventname, tagvalue order by eventdate) as seqnum
from tblEventRegister er
) er
group by tagname, eventname, seqnum
order by intime;
Цель nullif() состоит в том, чтобы справиться со случаем, когда нет следующего «выхода».
Вот скрипка db<>.