Требуется рекурсивная / циклическая справка для запроса с ROW_NUMBER
Я хочу иметь возможность проследить в обратном направлении через таблицу движения материалов, оборудования, использованного для его перемещения, и времени, начиная с указанного значения в поле и находя это значение в другом поле в другой строке (строках) на основе общего значения,
В приведенной ниже таблице мне нужно иметь возможность проследить, например, от EndCode 'M' все связанные строки вплоть до StartCode A. Затем я мог бы захотеть иметь возможность проследить все строки из EndCode "U" вернуться к StartCode "N".
В таблице StartCode (материал) A и B перемещаются, чтобы стать EndCode C. Затем EndCode C становится StartCodeD, затем перемещается вместе с StartCode E, чтобы стать EndCode F и т. Д. Оранжевые / синие строки представляют группы движений материала.
Исходя из этого, я хочу создать новую таблицу / представление, которое переносит время StartedAt следующего события в качестве нового поля с именем 'EndedAt'. Это будет выглядеть так:
Я создал рекурсивный запрос, который использует ROW_NUMBER и CTE для передачи через StartedAt в качестве нового поля как "EndedAt".
Затем я попытался использовать вложенный оператор SELECT, чтобы найти все StartCodes, связанные с EndCode 'M', в попытке выполнить обратную петлю через таблицу для поиска всех соответствующих EndCodes в столбце StartCode. Это приносит только несколько строк. Я повторяю вложенное утверждение несколько раз, когда оно проходит через разные строки.
Мне нужна помощь, чтобы подобрать все соответствующие начальные / конечные коды, необходимые для создания желаемой таблицы / представления.
Со временем к таблице будет прикреплен отчет, позволяющий фильтровать таблицу по выбранному параметру EndCode (например, M, U и т. Д.)
CREATE TABLE MyTable (
`StartCode` VARCHAR(1),
`StartedAt` DATETIME,
`EndCode` VARCHAR(1)
);
INSERT INTO MyTable (`StartCode`, `StartedAt`, `EndCode`)
VALUES
('A', '01/01/2019 01:00', 'C'),
('B', '01/01/2019 02:15', 'C'),
('C', '01/01/2019 03:00', 'F'),
('D', '01/01/2019 03:19', 'F'),
('E', '01/01/2019 04:00', 'F'),
('F', '01/01/2019 04:14', 'G'),
('G', '01/01/2019 05:00', 'J'),
('H', '01/01/2019 05:37', 'J'),
('I', '01/01/2019 05:45', 'J'),
('J', '01/01/2019 06:00', 'L'),
('K', '01/01/2019 06:09', 'L'),
('L', '01/01/2019 07:00', 'M'),
('N', '01/01/2019 09:20', 'P'),
('O', '01/01/2019 09:37', 'P'),
('P', '01/01/2019 09:45', 'Q'),
('Q', '01/01/2019 11:00', 'T'),
('R', '01/01/2019 11:10', 'T'),
('S', '01/01/2019 11:47', 'T'),
('T', '01/01/2019 11:58', 'U');
1 ответ
EndedAt это простое соединение:
SELECT
S.StartCode,
S.StartedAt,
S.EndCode,
E.StartedAt AS EndedAt
FROM
MyTable AS S
LEFT JOIN MyTable AS E ON S.EndCode = E.StartCode
Результат:
StartCode StartedAt EndCode EndedAt
A 2019-01-01 01:00:00.000 C 2019-01-01 03:00:00.000
B 2019-01-01 02:15:00.000 C 2019-01-01 03:00:00.000
C 2019-01-01 03:00:00.000 F 2019-01-01 04:14:00.000
D 2019-01-01 03:19:00.000 F 2019-01-01 04:14:00.000
E 2019-01-01 04:00:00.000 F 2019-01-01 04:14:00.000
F 2019-01-01 04:14:00.000 G 2019-01-01 05:00:00.000
G 2019-01-01 05:00:00.000 J 2019-01-01 06:00:00.000
H 2019-01-01 05:37:00.000 J 2019-01-01 06:00:00.000
I 2019-01-01 05:45:00.000 J 2019-01-01 06:00:00.000
J 2019-01-01 06:00:00.000 L 2019-01-01 07:00:00.000
K 2019-01-01 06:09:00.000 L 2019-01-01 07:00:00.000
L 2019-01-01 07:00:00.000 M NULL
N 2019-01-01 09:20:00.000 P 2019-01-01 09:45:00.000
O 2019-01-01 09:37:00.000 P 2019-01-01 09:45:00.000
P 2019-01-01 09:45:00.000 Q 2019-01-01 11:00:00.000
Q 2019-01-01 11:00:00.000 T 2019-01-01 11:58:00.000
R 2019-01-01 11:10:00.000 T 2019-01-01 11:58:00.000
S 2019-01-01 11:47:00.000 T 2019-01-01 11:58:00.000
T 2019-01-01 11:58:00.000 U NULL
Вы можете отобразить иерархии следующим образом (в данном случае рекурсивный CTE с подходом снизу вверх). Сначала убедитесь, что в ваших данных нет петель.
Изменить: Если вы хотите проверить любой шаг в иерархии и выше, якорь должен быть любой код (не только последний M или же U), поэтому я удалила WHERE в якоре.
DECLARE @EndCodeFilter CHAR(1) = 'J'
;WITH RecursiveCodes AS
(
-- Anchor
SELECT
LastCode = M.EndCode,
CurrentCode = M.StartCode,
PreviousCode = M.EndCode,
RecursionLevel = 1,
RecursionPath = CONVERT(NVARCHAR(MAX), M.EndCode + '->' + M.StartCode),
CurrentStartAt = M.StartedAt
FROM
MyTable AS M
UNION ALL
-- Recursion: link related codes
SELECT
LastCode = R.LastCode,
CurrentCode = M.StartCode,
PreviousCode = M.EndCode,
RecursionLevel = R.RecursionLevel + 1,
RecursionPath = R.RecursionPath + '->' + M.StartCode,
CurrentStartAt = M.StartedAt
FROM
RecursiveCodes AS R
INNER JOIN MyTable AS M ON R.CurrentCode = M.EndCode
)
SELECT
R.CurrentCode,
R.CurrentStartAt,
R.LastCode,
EndedAt = E.StartedAt,
R.PreviousCode,
R.RecursionLevel,
R.RecursionPath
FROM
RecursiveCodes AS R
LEFT JOIN MyTable AS E ON R.LastCode = E.StartCode
WHERE
R.LastCode = @EndCodeFilter
ORDER BY
R.CurrentCode,
R.LastCode
OPTION
(MAXRECURSION 0)
Результат:
CurrentCode CurrentStartAt LastCode EndedAt PreviousCode RecursionLevel RecursionPath
A 2019-01-01 01:00:00.000 J 2019-01-01 06:00:00.000 C 4 J->G->F->C->A
B 2019-01-01 02:15:00.000 J 2019-01-01 06:00:00.000 C 4 J->G->F->C->B
C 2019-01-01 03:00:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->C
D 2019-01-01 03:19:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->D
E 2019-01-01 04:00:00.000 J 2019-01-01 06:00:00.000 F 3 J->G->F->E
F 2019-01-01 04:14:00.000 J 2019-01-01 06:00:00.000 G 2 J->G->F
G 2019-01-01 05:00:00.000 J 2019-01-01 06:00:00.000 J 1 J->G
H 2019-01-01 05:37:00.000 J 2019-01-01 06:00:00.000 J 1 J->H
I 2019-01-01 05:45:00.000 J 2019-01-01 06:00:00.000 J 1 J->I