У меня есть таблица в базе данных SQL Server 2008 R2, которая определяет переходы между различными состояниями.
Пример соответствующих столбцов таблицы:
TransitionID | SourceStateID | DestinationStateID | WorkflowID
--------------------------------------------------------------
1 | 17 | 18 | 3
2 | 17 | 21 | 3
3 | 17 | 24 | 3
4 | 17 | 172 | 3
5 | 18 | 17 | 3
6 | 18 | 24 | 3
7 | 18 | 31 | 3
8 | 21 | 17 | 3
9 | 21 | 20 | 3
10 | 21 | 141 | 3
11 | 21 | 184 | 3
... так далее.
Моя цель состоит в том, чтобы иметь возможность определить начальный StateID, конечный StateID и WorkflowID и, надеюсь, найти логический путь от начального StateID к конечному StateID внутри этого рабочий процесс.
например Для приведенной выше таблицы, если бы я указал начальный StateID, равный 17, и конечный StateID, равный 184, и WorkflowID, равный 3, я мог бы получить «путь» 17>21>184 (или, что более идеально, TransitionID 2 > TransitionID 11)
Хорошее:
- Определенные начальный и конечный StateID будут всегда иметь возможный путь в пределах определенного WorkflowID.
Плохое:
Безусловно, существуют циклические ссылки практически на каждом StateID источника/назначения (т. е., вероятно, существует переход от SourceStateID 1 к DestinationStateID 2, а также от SourceStateID 2 к DestinationStateID 1).
Конечно, существует несколько возможных путей практически из любого определенного начального StateID и конечного StateID.
Я понимаю, что это какой-то CTE, который мне нужен, но, по общему признанию, я нахожу CTE трудным для понимания в целом, и вдвойне, когда круговые ссылки являются гарантированной проблемой.
Идеальным решением будет тот, который выбирает кратчайший путь от начального StateID до конечного StateID, но, если быть честным, на данный момент, если я смогу получить что-то работающее, что надежно даст мне любой действительный путь между два состояния я буду так счастлив.
Любые гуру SQL могут указать мне направление? Я, честно говоря, даже не знаю, с чего начать, чтобы предотвратить круговые проблемы, такие как получение пути по строкам 17> 18> 17> 18> 17> 18...
ОТВРАТИТЕЛЬНОЕ ОБНОВЛЕНИЕ Я придумал этот запрос, который причиняет мне боль на эмоциональном уровне (с некоторой помощью этого поста: https://stackoverflow.com/a/11042012/3253311), но, похоже, работает.
DECLARE @sourceStatusId INT = 17
DECLARE @destinationStatusId INT = 24
DECLARE @workflowId INT = 3
DECLARE @sourceStatusIdString NVARCHAR(MAX) = CAST(@sourceStatusId AS NVARCHAR(MAX))
DECLARE @destinationStatusIdString NVARCHAR(MAX) = CAST(@destinationStatusId AS NVARCHAR(MAX))
DECLARE @workflowIdString NVARCHAR(MAX) = CAST(@workflowId AS NVARCHAR(MAX))
;WITH CTE ([Source], [Destination], [Sentinel]) AS
(
SELECT
CAST(t.[Source] AS NVARCHAR(MAX)) AS [Source],
CAST(t.[Destination] AS NVARCHAR(MAX)) AS [Destination],
[Sentinel] = CAST([Source] AS NVARCHAR(MAX))
FROM
Transitions t
WHERE
CAST([Source] AS NVARCHAR(MAX)) = @sourceStatusIdString AND
CAST([WorkflowID] AS NVARCHAR(MAX)) = @workflowIdString
UNION ALL
SELECT
CAST(CTE.[Destination] AS NVARCHAR(MAX)),
CAST(t.[Destination] AS NVARCHAR(MAX)),
CAST([Sentinel] AS NVARCHAR(MAX)) + CAST('|' AS NVARCHAR(MAX)) + CAST(CTE.[Destination] AS NVARCHAR(MAX))
FROM
CTE
JOIN Transitions t
ON CAST(t.[Source] AS NVARCHAR(MAX)) = CAST(CTE.[Destination] AS NVARCHAR(MAX))
WHERE
CHARINDEX(CTE.[Destination], Sentinel)=0
)
SELECT TOP 1
[Sentinel]
FROM
CTE
WHERE
LEFT([Sentinel], LEN(@sourceStatusIdString)) = @sourceStatusIdString AND
RIGHT([Sentinel], LEN(@destinationStatusIdString)) = @destinationStatusIdString
ORDER BY
LEN([Sentinel])
