Блог

Главная - Вопросы по программированию - выберите строки с событиями, связанными с другими событиями в том же столбце запроса

выберите строки с событиями, связанными с другими событиями в том же столбце запроса

#sql #sql-server #tsql #union

#sql #sql-сервер #tsql #объединение

Вопрос:

Мне нужно выбрать строки с EventTypeID = 19, которые не имеют связанных EventtypeID = 21, зарегистрированных ровно на 4 минуты раньше для того же идентификатора сотрудника. Ниже приведен запрос и некоторые исходные данные:

 SELECT * FROM
    (
        SELECT rcp..EventLogEntries.EmployeeID, rcp..EventLogEntries.EventTypeID, rcp..EventLogEntries.TerminalID, rcp..EventLogEntries.LoggedOn
        FROM rcp..EventLogEntries
        WHERE rcp..EventLogEntries.terminalid = 3
        UNION
        SELECT viso..AccessUserPersons.UserExternalIdentifier, rcp..EventTypes.ID, rcp..Terminals.ID, viso..EventLogEntries.LoggedOn
        FROM viso..EventLogEntries, viso..AccessUserPersons, rcp..Terminals, rcp..EventTypes
        WHERE viso..EventLogEntries.LocationID = 10
        AND viso..EventLogEntries.EventCode = 615
        AND rcp..EventTypes.Code = 36
        AND viso..EventLogEntries.PersonID = viso..AccessUserPersons.ID
        AND viso..EventLogEntries.locationID = rcp..Terminals.TerminalTAID
    ) results
ORDER BY LoggedOn


EmployeeID  EventTypeID TerminalID  LoggedOn
273         19          3           2018-12-04 12:31:23.000
273         21          3           2018-12-04 12:34:18.000
483         19          3           2018-12-04 12:40:10.000
268         19          3           2018-12-04 13:19:23.000
273         21          3           2018-12-04 13:28:00.000
273         19          3           2018-12-04 13:32:00.000
459         19          3           2018-12-04 15:01:04.000

Чего мне нужно добиться, так это:

 EmployeeID  EventTypeID TerminalID  LoggedOn
273         19          3           2018-12-04 12:31:23.000
483         19          3           2018-12-04 12:30:10.000
268         19          3           2018-12-04 13:19:23.000
459         19          3           2018-12-04 15:01:04.000

Значение столбца TerminalID всегда равно 3 в этом сценарии, и оно не связано ни с каким условием запроса, но должно быть в выходных данных для требования синтаксиса при дальнейшей обработке.

Комментарии:

1. На самом деле это не минимальный запрос, и кому-то будет сложно протестировать ваш фактический код, предполагая, что у них даже были образцы данных, которые вы никогда не предоставляли. Кроме того, вы используете объединения в стиле старой школы.

Ответ №1:

Плохая практика объединения таблиц с использованием условий в WHERE . Блок WHERE необходимо использовать для фильтрации в первую очередь. А псевдонимы помогают сократить код.

 SELECT * FROM
    (
        SELECT EmployeeID, EventTypeID, TerminalID, LoggedOn
        FROM rcp..EventLogEntries
        WHERE terminalid = 3
        UNION
        SELECT p.UserExternalIdentifier, et.ID, t.ID, el.LoggedOn
        FROM viso..EventLogEntries el
        JOIN viso..AccessUserPersons p ON el.PersonID = p.ID
        JOIN rcp..Terminals t ON el.locationID = t.TerminalTAID
        JOIN rcp..EventTypes et ON --!!! no any condition here
        WHERE el.LocationID = 10
          AND el.EventCode = 615
          AND et.Code = 36
    ) results
ORDER BY LoggedOn

Попробуйте использовать следующее:

 WITH cteData AS
    (
        SELECT EmployeeID, EventTypeID, TerminalID, LoggedOn
        FROM rcp..EventLogEntries
        WHERE terminalid = 3
        UNION
        SELECT p.UserExternalIdentifier, et.ID, t.ID, el.LoggedOn
        FROM viso..EventLogEntries el
        JOIN viso..AccessUserPersons p ON el.PersonID = p.ID
        JOIN rcp..Terminals t ON el.locationID = t.TerminalTAID
        JOIN rcp..EventTypes et ON --!!! no any condition here
        WHERE el.LocationID = 10
          AND el.EventCode = 615
          AND et.Code = 36
    )
SELECT q19.*
FROM
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=19
  ) q19
LEFT JOIN
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=21
  ) q21
ON q19.EmployeeID=q21.EmployeeID
WHERE (DATEDIFF(MINUTE,q19.LoggedOn,q21.LoggedOn)>4 OR q21.LoggedOn IS NULL)

Если вам не нужны какие-либо условия, вы можете использовать CROSS JOIN .

Я получил ваш результат, используя ваши данные:

 WITH cteData AS(
  SELECT *
  FROM (VALUES
        (273,19,3,CAST('2018-12-04 12:31:23.000' AS datetime)),
        (273,21,3,CAST('2018-12-04 12:34:18.000' AS datetime)),
        (483,19,3,CAST('2018-12-04 12:40:10.000' AS datetime)),
        (268,19,3,CAST('2018-12-04 13:19:23.000' AS datetime)),
        (273,21,3,CAST('2018-12-04 13:28:00.000' AS datetime)),
        (273,19,3,CAST('2018-12-04 13:32:00.000' AS datetime)),
        (459,19,3,CAST('2018-12-04 15:01:04.000' AS datetime))
    )v(EmployeeID,EventTypeID,TerminalID,LoggedOn)
)
SELECT q19.*
FROM
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=19
  ) q19
LEFT JOIN
  (
    SELECT *
    FROM cteData
    WHERE EventTypeID=21
  ) q21
ON q19.EmployeeID=q21.EmployeeID
WHERE (DATEDIFF(MINUTE,q19.LoggedOn,q21.LoggedOn)>4 OR q21.LoggedOn IS NULL)

Ответ №2:

В этой части сделано небольшое изменение:

 ON q19.EmployeeID=q21.EmployeeID AND q19.LoggedOn=dateadd(mi, 4, q21.LoggedOn)
WHERE q21.LoggedOn IS NULL
Метки: