Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка дерева с заданной позицией элемента  [new]
igas
Member

Откуда: Minsk
Сообщений: 175
Приветствую Всех!

Проблема в следующем, нужно получить дерево с заданной позицией элемента
Структура дерева выглядит вот так:
[1]
|_[3]
|_[8]
|_[2]
| |_[5]
| |_[9]
|
|_[6]
| |_[4]
|
|_[7]

Tаблица имеет следующую структуру

TYPE KEY PARENTKEY NESTEDKEY NEXTKEY PREVKEY
0 1 0 3 0 0
0 2 1 5 6 8
0 3 1 0 8 0
0 4 6 0 0 0
0 5 2 0 9 0
0 6 1 4 7 2
0 7 1 0 0 6
0 8 1 0 2 3
0 9 2 0 0 5

где PARENTKEY - ключ родителя
NESTEDKEY - ключ первого потомка если есть
NEXTKEY - ключ элемента следующего за текущим
PREVKEY - ключ предыдущего элемента

Может кто подскажет как лучше организовать запрос.
6 фев 14, 23:35    [15531571]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
sdet
Member

Откуда:
Сообщений: 463
igas,
Рекурсивный CTE
7 фев 14, 00:01    [15531685]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
igas
Member

Откуда: Minsk
Сообщений: 175
sdet,
Не совсем подойдёт т.к. SQL Server 2000 не поддерживает CTE, а мне кросс-серверный скрипт,
пытался с использованием CTE написать, вот что получилось, правда задачу решает не полностью выводит только потомков конкретного узла(буду благодарен за подсказку как сделать чтобы выводилась полная иерархия).
WITH HierarchycalView ([TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]) 
AS
(
	SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]
	FROM [Hierarchy]
	WHERE [TYPE] = 0 AND [PREVKEY] = 0 AND [NEXTKEY] <> 0 AND [PARENTKEY] = 1
	UNION ALL
	SELECT [H].[TYPE], [H].[KEY], [H].[PARENTKEY], [H].[NESTEDKEY], [H].[NEXTKEY], [H].[PREVKEY]
	FROM [Hierarchy] as H
	INNER JOIN HierarchycalView as HV ON  [H].[KEY] = [HV].[NEXTKEY]
	WHERE [H].[TYPE] = 0
)
SELECT * FROM HierarchycalView


Конечно можно решить задачу в лоб, но хотелось бы получить рекомендации как лучше это сделать
+ Сложный путь

SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]
FROM [Hierarchy]
WHERE [TYPE] = 0 AND [PREVKEY] = 0 AND [NEXTKEY] <> 0 AND [PARENTKEY] = 1
UNION
SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]
FROM [Hierarchy] 
WHERE [TYPE] = 0 AND [KEY] = (SELECT [NEXTKEY] FROM [Hierarchy] WHERE [TYPE] = 0 AND [PREVKEY] = 0 AND [NEXTKEY] <> 0 AND PARENTKEY] = 1)
UNION
SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]
FROM [Hierarchy] 
WHERE [TYPE] = 0 AND [KEY] = (SELECT [NEXTKEY] FROM [Hierarchy] WHERE [TYPE] = 0 AND [KEY] = (SELECT [KEY] FROM [Hierarchy] WHERE [TYPE] = 0 AND [PREVKEY] = 0 AND [NEXTKEY] <> 0 AND PARENTKEY] = 1))
UNION
--.... и так далее

7 фев 14, 01:11    [15531838]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
sdet
Member

Откуда:
Сообщений: 463
igas,
Для SQL2000, CTE заменяется циклом
7 фев 14, 01:21    [15531848]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Glory
Member

Откуда:
Сообщений: 104760
igas
Конечно можно решить задачу в лоб, но хотелось бы получить рекомендации как лучше это сделать

BOL - Expanding Hierarchies
7 фев 14, 10:12    [15532676]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
igas
Member

Откуда: Minsk
Сообщений: 175
Получился такой скрипт
SET NOCOUNT ON
DECLARE @nested int, @next int, @parent int, @curkey int, @lvl int, @line varchar(20)
SET @curkey = 1
SET @lvl = 1
if object_id('tempdb..#HierarchycalView ') is not null 
drop table #HierarchycalView 
CREATE TABLE #HierarchycalView  ([TYPE] int, [KEY] int, [PARENTKEY] int, [NESTEDKEY] int, [NEXTKEY] int,[PREVKEY] int, lvl int)
WHILE @lvl > 0
  BEGIN
	INSERT #HierarchycalView 
		SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY], @lvl
			FROM [Hierarchy] WHERE [TYPE] = 457415 AND [KEY] = @curkey
	IF @@ROWCOUNT <> 0
	BEGIN
		SELECT @curkey = [KEY], @nested = [NESTEDKEY], @next = [NEXTKEY], @parent = [PARENTKEY] FROM #HierarchycalView WHERE lvl = @lvl
		SELECT @line = space(@lvl - 1) + CONVERT(varchar(16), @curkey) PRINT @line
		IF (@nested <> 0)
		BEGIN
			SET @curkey = @nested
			SELECT @lvl = @lvl + 1
		END
		ELSE IF ((@next <> 0)) SET @curkey = @next
		ELSE 
		BEGIN 
			SET @lvl = @lvl - 1
			SELECT @curkey = [NEXTKEY] FROM #HierarchycalView WHERE lvl = @lvl and [KEY] = @parent
		END
	END
	ELSE 
	BEGIN
		SELECT @curkey = [KEY], @nested = [NESTEDKEY], @next = [NEXTKEY], @parent = [PARENTKEY] FROM #HierarchycalView  WHERE lvl = @lvl
		IF ((@next <> 0)) SET @curkey = @next
		ELSE 
		BEGIN 
			SET @lvl = @lvl - 1
			SELECT @curkey = [NEXTKEY] FROM #HierarchycalView  WHERE lvl = @lvl and [KEY] = @parent
		END
	END
END


Может подскажете как упростить?
28 мар 14, 11:44    [15799677]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Эпично
igas
WITH HierarchycalView ([TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]) AS (
	SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY]
	FROM [Hierarchy]
	WHERE [TYPE] = 0 AND [PREVKEY] = 0 AND [NEXTKEY] <> 0 AND [PARENTKEY] = 1
UNION ALL
	SELECT [H].[TYPE], [H].[KEY], [H].[PARENTKEY], [H].[NESTEDKEY], [H].[NEXTKEY], [H].[PREVKEY]
	FROM [Hierarchy] as H
	INNER JOIN HierarchycalView as HV ON  [H].[KEY] = [HV].[NEXTKEY]
	WHERE [H].[TYPE] = 0
)SELECT * FROM HierarchycalView

igas
SET NOCOUNT ON
DECLARE @nested int, @next int, @parent int, @curkey int, @lvl int, @line varchar(20)
SET @curkey = 1
SET @lvl = 1
if object_id('tempdb..#HierarchycalView ') is not null 
drop table #HierarchycalView 
CREATE TABLE #HierarchycalView  ([TYPE] int, [KEY] int, [PARENTKEY] int, [NESTEDKEY] int, [NEXTKEY] int,[PREVKEY] int, lvl int)
WHILE @lvl > 0 BEGIN
	INSERT #HierarchycalView 
		SELECT [TYPE], [KEY], [PARENTKEY], [NESTEDKEY], [NEXTKEY], [PREVKEY], @lvl
			FROM [Hierarchy] WHERE [TYPE] = 457415 AND [KEY] = @curkey
	IF @@ROWCOUNT <> 0 BEGIN
		SELECT @curkey = [KEY], @nested = [NESTEDKEY], @next = [NEXTKEY], @parent = [PARENTKEY] FROM #HierarchycalView WHERE lvl = @lvl
		SELECT @line = space(@lvl - 1) + CONVERT(varchar(16), @curkey) PRINT @line
		IF (@nested <> 0) BEGIN
			SET @curkey = @nested
			SELECT @lvl = @lvl + 1
		END ELSE IF ((@next <> 0)) SET @curkey = @next
		ELSE BEGIN 
			SET @lvl = @lvl - 1
			SELECT @curkey = [NEXTKEY] FROM #HierarchycalView WHERE lvl = @lvl and [KEY] = @parent
		END
	END ELSE BEGIN
		SELECT @curkey = [KEY], @nested = [NESTEDKEY], @next = [NEXTKEY], @parent = [PARENTKEY] FROM #HierarchycalView  WHERE lvl = @lvl
		IF ((@next <> 0)) SET @curkey = @next
		ELSE BEGIN 
			SET @lvl = @lvl - 1
			SELECT @curkey = [NEXTKEY] FROM #HierarchycalView  WHERE lvl = @lvl and [KEY] = @parent
		END
	END
END
igas
Может подскажете как упростить?
Сам себе глаза ломай.
Форматировать код можно лучше.

NESTEDKEY, NEXTKEY, PREVKEY - то ещё извращение.
28 мар 14, 17:32    [15802502]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Glory
Member

Откуда:
Сообщений: 104760
igas
Может подскажете как упростить?

Скрипт из предложенной статьи хелпа
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1

WHILE @level > 0
BEGIN
   IF EXISTS (SELECT * FROM #stack WHERE level = @level)
      BEGIN
         SELECT @current = item FROM #stack WHERE level = @level
         SELECT @line = space(@level - 1) + @current
         PRINT @line
         DELETE FROM #stack WHERE level = @level AND item = @current
         INSERT #stack SELECT child, @level + 1 FROM hierarchy WHERE parent = @current
         IF @@ROWCOUNT > 0
            SELECT @level = @level + 1
      END
   ELSE
      SELECT @level = @level - 1
END -- WHILE
28 мар 14, 18:17    [15802686]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
igas
Member

Откуда: Minsk
Сообщений: 175
Glory,
Так я на основе его и делал, только этот скрипт для дерева, котором не учитывается порядок следования, что в моём случае не подходит.
31 мар 14, 11:34    [15809986]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Glory
Member

Откуда:
Сообщений: 104760
igas
котором не учитывается порядок следования

Что, извините, не учитывается ?
31 мар 14, 11:36    [15810000]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Glory,

Понятно же, ведь поэтому он и "связал" в CTE не через PARENTKEY, а через NEXTKEY.
Ибо нет у человека понимания о "порядке следования срок".
31 мар 14, 19:41    [15813338]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
igas
Member

Откуда: Minsk
Сообщений: 175
Mnior, скрипт CTE никакой смысловой нагрузки не несёт т.к. он не отражает некоторых нюансов, поэтому на него ссылаться не вижу смысла!
Glory, поясню на примере, исходная структура до сохранения в БД, к примеру такая:
[1]
|_[3]
|_[8]
|_[2]
| |_[5]
| |_[9]
|
|_[6]
| |_[4]
|
|_[7]
То после выборки она должна быть такой же.
А в примере из BOL она может стать и такой и какой-нибудь ещё
[1]
|_[7]
|_[3]
|_[6]
| |_[4]
|
|_[2]
| |_[5]
| |_[9]
|
|_[8]
1 апр 14, 19:53    [15818180]     Ответить | Цитировать Сообщить модератору
 Re: Выборка дерева с заданной позицией элемента  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
igas
скрипт CTE никакой смысловой нагрузки не несёт т.к. он не отражает некоторых нюансов, поэтому на него ссылаться не вижу смысла!
Как раз несёт ибо вы всё ещё не догоняете проблематику "порядок следования срок".

"Выборка дерева", "получить дерево" в стандартной тематике, 99.99% любых задач SQL, точнее в реляционной алгебре - порядок строк не имеет никакого смысла.
Хоть данные придут так:
TYPEKEYPARENTKEYNESTEDKEYNEXTKEYPREVKEY
092005
081023
071006
061472
052090
046000
031080
021568
010300
Это одни и теже данные. В любой комбинации.
Но так как иногда (чаще из-за убогости систем, построения клиентов) можно порядок указывать.
но это делается одним внешним оператором ORDER BY. Только он (по спецификации) гарантирует порядок строк. Т.е. можно подготовить внутренние условия сервера так, что вы при одном и том же запросе на одних и тех же данных будете получать разный порядок строк каждый раз. Да! на простом запросе вида:
SELECT * FROM #HierarchycalView


Если ваш клиент требует порядок строк, то скорее лучше посоветовать выкинуть ваш клиент отображения (что у вас там COBOL чтоле или DELPHI 3.1 ? Видимо поэтому ещё увас 2000й сервер) и начать пользоваться нормальными контролами.

Но корни глубже.
Mnior
NESTEDKEY, NEXTKEY, PREVKEY - то ещё извращение.
Часто добавляют к Parent колонку Order, а все вами предложенные колонки это избыточная информация в неуодобоваримом виде.
А два классических варианта представления деревьев (из трёх) в базе вообще сводият всё к одному тупому запросу.
2 апр 14, 02:13    [15819283]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить