SQL для переупорядочения узлов в иерархии

Question

У меня есть база данных "список задач", которая использует модель списка смежности (см. Ниже), поэтому у каждой "задачи" может быть неограниченное количество подзадач. В таблице есть столбец TaskOrder, поэтому все отображается в правильном порядке в древовидной структуре.

Существует ли оператор SQL (MS-SQL 2005), который выберет все дочерние узлы для указанного родителя и обновит столбец TaskOder при удалении родного брата?

Таблица задач
----------
TaskId
ParentTaskId
TaskOrder
TaskName
--так далее--

Есть идеи? Благодарю.

Answer 1

Пара разных способов... Так как TaskOrder ограничен родительским идентификатором, собрать его не очень сложно. В SQL Server я бы добавил триггер на удаление, который уменьшает все "выше", чем тот, который вы удалили, тем самым закрывая пробел (псевдокод следует):

CREATE TRIGGER ON yourtable FOR DELETE
AS
  UPDATE Task
     SET TaskOrder    = TaskOrder - 1
   WHERE ParentTaskId = deleted.ParentTaskId
     AND TaskOrder    > deleted.TaskOrder

Если вам не нужен триггер, вы можете сначала захватить parentID и TaskOrder в запросе, удалить строку, а затем выполнить тот же оператор обновления, но с литералами, а не с триггером.

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

Answer 2

Если вы используете TaskOrder только для сортировки, проще было бы просто оставить дыры в TaskOrder, потому что простое удаление элементов не сделает сортировку некорректной. Но тогда я не уверен насчет потребностей вашего приложения.

Answer 3

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

Если предположить, что порядок детей внутри родителя не имеет значения, то топологическая сортировка даст вам то, что вы хотите. Вы не получите это в одном запросе на большинстве диалектов SQL - вам придется написать sproc, чтобы сделать это.

Если порядок дочерних элементов в узле является релевантным, необходимо поддерживать порядок задач в родительском узле. Запрос, использующий ParentNodeID, TaskOrder и count (*), выберет дубликаты, но если в системе нет дополнительной информации для заказа задач, вам все равно потребуется ручное вмешательство для выбора правильного порядка.

Пожалуйста, добавьте комментарии, если вы хотите, чтобы я кое-что прояснил.

Answer 4

Удалить задание 88:

UPDATE TaskTable
SET ParentTaskID = (SELECT ParentTaskID AS temp FROM Task_Table t1 WHERE TaskID = 88)
WHERE
TaskID IN (SELECT TaskID task2 FROM TaskTable t2 WHERE ParentTaskID = 88);
Delete FROM TaskTable WHERE TaskID = 88;

Конечно, вы можете исключить удаление и просто оставить запись без дела для будущих отчетов.

ПРЕДУПРЕЖДЕНИЕ: НЕ ИСПЫТАНО!!!

Answer 5

Это похоже на работу для ROW_Number.

DECLARE @Tasks TABLE
(
  TaskId int PRIMARY KEY,
  ParentTaskId int,
  TaskOrder int,
  TaskName varchar(30)
)

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 1, null, 1, 'ParentTask'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 2, 1, 2, 'B'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 3, 1, 1, 'A'

INSERT INTO @Tasks(TaskId, ParentTaskId, TaskOrder, TaskName)
SELECT 4, 1, 3, 'C'
--Initial
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder

DELETE FROM @Tasks WHERE TaskId = 2
--After Delete
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder


UPDATE t
SET TaskOrder = NewTaskOrder
FROM @Tasks t
  JOIN
(
SELECT TaskId, ROW_Number() OVER(ORDER BY TaskOrder) as NewTaskOrder
FROM @Tasks
WHERE ParentTaskId = 1
) sub ON t.TaskId = sub.TaskId

--After Update
SELECT * FROM @Tasks WHERE ParentTaskId = 1 ORDER BY TaskOrder