Как можно вывести связанные записи в одной таблице в MS SQL?

Question

Имеется таблица со списком задач

В таблице есть поля с ссылками на предыдущую (link_pred_uid) и на последующую (link_succ_uid) задачи. Как можно вывести список всех связанных задач по входящему параметру task_uid (к сожалению task_uid и link_uid имеют разные значения), т.е. если на вход идет параметр task_uid = 11, то вывести предыдущую, текущую и последующую задачи:

Upd:

USE [db]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[dependence](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](255) NULL,
    [task_uid] [int] NULL,
    [link_pred_uid] [int] NULL,
    [link_uid] [int] NULL,
    [link_succ_uid] [int] NULL,
 CONSTRAINT [PK_dependence] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[dependence]
           ([name]
           ,[task_uid]
           ,[link_pred_uid]
           ,[link_uid]
           ,[link_succ_uid])
     VALUES
           ('покраска'
           ,10
           ,4
           ,2
           ,2)
GO
INSERT INTO [dbo].[dependence]
           ([name]
           ,[task_uid]
           ,[link_pred_uid]
           ,[link_uid]
           ,[link_succ_uid])
     VALUES
           ('купить краску'
           ,11
           ,6
           ,4
           ,2)
GO
INSERT INTO [dbo].[dependence]
           ([name]
           ,[task_uid]
           ,[link_pred_uid]
           ,[link_uid]
           ,[link_succ_uid])
     VALUES
           ('построить забор'
           ,12
           ,6
           ,6
           ,4)
GO
INSERT INTO [dbo].[dependence]
           ([name]
           ,[task_uid]
           ,[link_pred_uid]
           ,[link_uid]
           ,[link_succ_uid])
     VALUES
           ('сварка'
           ,13
           ,10
           ,8
           ,8)
GO
INSERT INTO [dbo].[dependence]
           ([name]
           ,[task_uid]
           ,[link_pred_uid]
           ,[link_uid]
           ,[link_succ_uid])
     VALUES
           ('купить трубу'
           ,14
           ,10
           ,10
           ,8)

при WHERE task_uid = 11, то результат

при WHERE task_uid = 13, то

Answer 1

Общий алгоритм такой:

По task_uid найти строку, вывести ее, запомнить link_pred_uid и link_succ_uid
Найти соседей
- Найти предка по link_pred_uid,
  если есть - вывести, запомнить его link_pred_uid,
  если нету - обнулить link_pred_uid.
- Найти потомка по link_succ_uid,
  если есть - вывести, запомнить его link_succ_uid,
  если нету - обнулить link_succ_uid.
Повторять пункт 2, пока находится хотя бы один сосед.

Можно реализовать через рекурсивный запрос самосоединением (гуглить ключевые слова WITH, CTE)

Также можно сделать циклом в табличной функции.

По синтаксису не подскажу, MS SQL под рукой нету

Answer 2

with CTE as(
    select *, 3 updown from dependence where task_uid=11
  union all
    select d.*, case when d.link_uid = c.link_pred_uid then 1 else 2 end
      from CTE c, dependence d
     where (c.link_uid!=c.link_pred_uid and d.link_uid = c.link_pred_uid and c.updown & 1 != 0)
        or (c.link_uid!=c.link_succ_uid and d.link_uid = c.link_succ_uid and c.updown & 2 != 0)
)
select * from CTE

В общем то обычный рекурсивный CTE. Единственное нестандартное решение - указание разрешенного направления движения по ссылкам в битовом поле updown. 1 - вверх (к родителю), 2 - вниз.

Вообще структура таблицы оставляет желать лучшего. Установка полей link_pred_uid в то же значение что link_uid потенциально могла бы приводить к рекурсии в подобных запросах и это всегда надо учитывать (условие c.link_uid!=c.link_pred_uid). Лучше было бы ставить значение NULL в конце цепочки. Кроме того двусторонняя связь требует особого внимания и потенциально может оказаться не корректной. Например дочерняя запись указанная в succ будет указывать на другого родителя в pred. Это обязывает любые записи менять парами в пределах одной транзакции и лучше бы контролировать триггерами. Любые нарушения ссылок в БД скорее всего будут приводить данный запрос к аварийному завершению в связи с зацикливанием.

Пример на sqlfiddle.com

БЛОГ НА HUSL

Как можно вывести связанные записи в одной таблице в MS SQL?

Ответы (2 шт):