Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Сортировка дерева  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Дерево хранится так:
Products (Products_ID, parent_ID, isFolder, Name)
Products_tree (Products_tree_ID, Products_ID, Parent_ID)
То есть в таблице Products хранится инфа о товаре (с указанием прямого родителя), а в таблице Products_tree - дерево, т.е. соответствия данного товара всем его родителям, заканчивая root'ом.
Задача - получить рекордсет с товарами (без папок) с сортировкой по алфавиту С УЧЕТОМ ИЕРАРХИИ.
Пример:
- ААА (группа)
--- ААА2 (группа)
------ В2 (элемент)
------ А2 (элемент)
------ Б2 (элемент)
--- ААА1 (группа)
------ Б1 (элемент)
------ А1 (элемент)
------ В1 (элемент)- БББ (группа)
--- БББ1 (группа)
------ В3 (элемент)
------ Б3 (элемент)
------ А3 (элемент)
Нужно получить рекордсет:
Товар
------
А1
Б1
В1
А2
Б2
В2
А3
Б3
В3
2 июн 05, 16:18    [1592252]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
не оптимальное, вероятно, но простое
Create function dbo.getfulltreename( @product_id int) returns varchar(1000)
as
begin
   declare @tempresult varchar(1000)

   set @tempresult  = ''

   select @tempresult  =  @tempresult  + p.name
   from products p
          join product_tree pt
          on p.product_id  = pt.parent_id
          left join ( select product_id, count(*) as treelevel
                  from  product_tree pt1
                  group by  product_id       )  ptl-- уровень  в иерархии
          on p.product_id = ptl.product_id
    where pt.product_id = @product_id
    order by isnull( ptl.treelevel, 0)

    -- если в Products_tree нет записей с Products_id = parent_id
    select @tempresult  = @tempresult + name
     from products p
     where  p.product_id = @product_id
     
     return @tempresult  

end
GO

select p.* 
from products p
     left join product_tree pt
     on pt.parent_id = p.product_id
where pt.parent_id is null

order by dbo.getfulltreename( p.product_id)

2 июн 05, 17:01    [1592467]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Vladimir Kovalevskii
Member

Откуда:
Сообщений: 696
вопрос принципиальный, что за дерево?

если рекурсия, то всё просто: для этого тебе достаточно в таблице создать поле в котором хранить уровень вложенности, или является ли оно листом.

в общем вариантов много.


но это не самый хороший способ.
2 июн 05, 17:59    [1592709]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Александр Гладченко
Member

Откуда:
Сообщений: 10753
Блог
https://www.sql.ru/subscribe/2005/260.shtml#20
2 июн 05, 18:02    [1592716]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927

Я бы рассчитал по каждому узлу УРОВЕНЬ и ПОРЯДОК внути родителя. На основе
них - можно сортировать.

Для расчета уровня в дереве (с ограничением в 32 уровня, правда):
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=188088&hl=#1582186


Posted via ActualForum NNTP Server 1.2

2 июн 05, 19:41    [1592994]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
автор

Для расчета уровня в дереве (с ограничением в 32 уровня, правда):


Какой ужас. А я-то думал, что это мое решение не эффективно :)
2 июн 05, 20:07    [1593030]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Latuk
Member

Откуда: N 54°38', E 037°35'
Сообщений: 7310
>с ограничением в 32 уровня, правда
Любую рекурсию можно переделать в цикл
для T-SQL с циклом еще и быстрее должно заработать
2 июн 05, 20:21    [1593059]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
да там цикла никакого не надо, уровень дерева при этой структуре вычисляется как
 select count(*)
 from product_tree
 where product_id = @product_id
и ВСЕ.
2 июн 05, 20:29    [1593073]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Александр Гладченко
https://www.sql.ru/subscribe/2005/260.shtml#20

И что? Там вроде только структура дерева в базе. У меня всё так и хранится. Но вопрос-то не в этом..
Остальные предложенные варианты буду пробовать завтра..
2 июн 05, 20:32    [1593078]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Кот Матроскин
да там цикла никакого не надо, уровень дерева при этой структуре вычисляется как
  select count(*)
 from product_tree
 where product_id = @product_id
и ВСЕ.
и ЧТО? Вы хоть знаете, что такое уровень дерева?
2 июн 05, 20:34    [1593086]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Не уровень дерева - уровень элемента, это Вы верно поправили.
В общем, как раз то, что делает Ваша функция.
для элемента уровня так 10-го можете сравнить время выполнения :)
2 июн 05, 20:46    [1593111]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
 node 1 ----------- уровень 1
--- 1.1 ---------- уровень 2
--- 1.2 ---------- уровень 2
--- 1.3 ---------- уровень 2
node 2 ----------- уровень 1
--- 2.1 ---------- уровень 2
--- 2.2 ---------- уровень 2
-------- 2.2.1 --- уровень 3
-------- 2.2.3 --- уровень 3
--- 2.3 ---------- уровень 2
- ЭТО ВЫЧИСЛЯЕТ ПРЕДЛОЖЕННЫЙ ВАМИ СКРИПТ?

Сдается мне, что
select count(*) from product_tree where product_id = @product_id
всегда будет возвращать 1. Прокомментируйте ка. Что такое @product_id?

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 20:54    [1593133]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
А порядковый номер внутри узла можно рассчитать например вот так:
	DECLARE @PAR INT, @R INT, @NAME VARCHAR(300)
	SELECT @PAR = ParentID, @NAME=NodeName FROM Nodes WHERE nodeID = @id

   	SELECT @R=(ISNULL(COUNT(*),0)+1) 
	FROM Nodes WHERE 
				ParentID=@PAR 
			AND ((NodeName < @NAME) OR (NodeName=@Name AND NodeID<@ID))
			AND NOT (NodeID=@ID)

	RETURN @R

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 21:07    [1593164]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Чтобы сортировать дерево можно вычислить вот такой код узлов:

 node 1 ----------- уровень 1 ----------   001
--- 1.1 ---------- уровень 2 ---------- 001001
--- 1.2 ---------- уровень 2 ---------- 001002
--- 1.3 ---------- уровень 2 ---------- 001003
node 2 ----------- уровень 1 ---------- 002
--- 2.1 ---------- уровень 2 ---------- 002001
--- 2.2 ---------- уровень 2 ---------- 002002
-------- 2.2.1 --- уровень 3 ---------- 002002001
-------- 2.2.3 --- уровень 3 ---------- 002002002
--- 2.3 ---------- уровень 2 ---------- 002003

делается той же рекурсией, как и вычисляется уровень.

Суть - к КОДУ родителя приклеить код узла, который представляет порядковый номер узла внутри детей его родителя, переведенный в строку фиксированнй длины.

Ф-я конвертации числа 3 в строку 00003

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 21:25    [1593198]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Для форматирования чисел есть еще встроенная функция: xp_sprintf

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 21:27    [1593204]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
автор

ЭТО ВЫЧИСЛЯЕТ ПРЕДЛОЖЕННЫЙ ВАМИ СКРИПТ?

Нет. И Ваша функция
CREATE FUNCTION dbo.node_level ( @id int )
RETURNS INT AS  
BEGIN 
	DECLARE @R INT, @PAR INT
	SELECT @PAR = ParentID FROM Nodes WHERE NodeID=@id
 	IF (@par = 0) OR (@par is null)
	BEGIN
		SET @R = 0
	END ELSE BEGIN
		SET @R = dbo.node_level ( @PAR ) + 1
	END
	RETURN @R
END


этого не вычисляет - как несложно видеть, она ничего не выводит и возвращает 1(одно) число для заданного id элемента.
ровно тоже самое делает мой скрипт - это же самое число для элемента
@product_id. Не согласны? приведите данные в описанных таблицах, для которых это не так.


автор

Сдается мне, что select count(*) from product_tree where product_id = @product_id

всегда будет возвращать 1.

Вам неверно сдается. Читайте внимательнее описание исходных таблиц.
2 июн 05, 21:31    [1593211]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Матроскин
как несложно видеть, она ничего не выводит
плохо смотрите
1) что значит "ВЫВОДИТ" ?????
2) она возвращает INT
Матроскин
этого не вычисляет
функция вычисляет УРОВЕНЬ узла. Я не собираюсь тебе это доказывать, т.к. это рабочий вариант, проверенный и использующийся в данный момент на практике, и работает отлично.
Матроскин
возвращает 1(одно) число для заданного id элемента
а сколько надо ? :)) двадцать? тридцать?
2 июн 05, 22:07    [1593257]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Latuk
>с ограничением в 32 уровня, правда Любую рекурсию можно переделать в цикл для T-SQL с циклом еще и быстрее должно заработать
Я понимаю. Это мне ГСА пальцем ткнул Я думаю и 32 выше крыши!
https://www.sql.ru/forum/actualthread.aspx?bid=1&tid=188088&hl=#1582320
2 июн 05, 22:14    [1593266]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
автор

функция вычисляет УРОВЕНЬ узла.

Зачем тогда в качестве результата ее работы приводилась таблица?




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

Да, она работает - с этим никто не спорит. Но делает это очень, очень
неэффективно.
2 июн 05, 22:19    [1593272]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
1) Читайте внимательно книжки. RETURN INT - означает, что функция возвращает число, а не таблицу.
2) Покажите пальцем где я приводил таблицу в качестве ее работы??? Про функцию я написал: "Для расчета уровня в дереве: ... "
3) В приведенной таблице были: "уровень такой-то...", "уровень такой-то..." и "уровень такой-то..."
4) Я и не заявлял что она супербыстрая

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 22:27    [1593277]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
BusyMan

node 1 ----------- уровень 1
--- 1.1 ---------- уровень 2
--- 1.2 ---------- уровень 2
--- 1.3 ---------- уровень 2
node 2 ----------- уровень 1
--- 2.1 ---------- уровень 2
--- 2.2 ---------- уровень 2
-------- 2.2.1 --- уровень 3
-------- 2.2.3 --- уровень 3
--- 2.3 ---------- уровень 2


Это было к чему? Вы спросили, вычисляет ли это мой скрипт(в отличие от Вашей функции). Я ответил, что и Ваша функция данной таблицы
не формирует, ни в виде resultset'а, ни в виде вывода на консоль.

BusyMan

4) Я и не заявлял что она супербыстрая

То есть Вы согласны с тем, что она неэффективна и что то же самое при данной структуре таблиц можно вычислить гораздо проще и быстрее(например, моим скриптом)?
В общем-то, это я и утверждал :)
2 июн 05, 22:41    [1593290]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
1) "ЭТО" не тоже самое, что "ЭТА ТАБЛИЦА"
2) Не корректно выдирать кусок кода (такой как вы выделнули !!!) и потом заявлять, мол смотрите выше какие там таблицы...
3) я считаю что предложенный мною метод для большинства задач вполне эффективен
4) "при данной структуре таблиц" мжно сделать еще проще чем вы демаете. Я то привел более универсальный путь
5) ЧТО УГОДНО можно сделать быстрее и проще (хотя и то и другое - понятия относительные)
6) То, что ваша будет быстрее я и не опровергаю и не подтверждаю. Не хватало еще умом мериться - какую зотите такую и используйте. Т.к., результат сильно зависит от данных и их объема и пр. Я думаю, что автор топика скрестит все предложенные варианты и у него родится его собственный

P.S. Можно просто Бизик admin@busyman.ru ICQ# 131833549
2 июн 05, 22:48    [1593296]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Кот Матроскин
не оптимальное, вероятно, но простое
Create function dbo.getfulltreename( @product_id int) returns varchar(1000)
as
begin
   declare @tempresult varchar(1000)

   set @tempresult  = ''

   select @tempresult  =  @tempresult  + p.name
   from products p
          join product_tree pt
          on p.product_id  = pt.parent_id
          left join ( select product_id, count(*) as treelevel
                  from  product_tree pt1
                  group by  product_id       )  ptl-- уровень  в иерархии
          on p.product_id = ptl.product_id
    where pt.product_id = @product_id
    order by isnull( ptl.treelevel, 0)

    -- если в Products_tree нет записей с Products_id = parent_id
    select @tempresult  = @tempresult + name
     from products p
     where  p.product_id = @product_id
     
     return @tempresult  

end
GO

select p.* 
from products p
     left join product_tree pt
     on pt.parent_id = p.product_id
where pt.parent_id is null

order by dbo.getfulltreename( p.product_id)



Не работает.. :( Запрос:
   select @tempresult  =  @tempresult  + p.name
   from products p
          join product_tree pt
          on p.product_id  = pt.parent_id
          left join ( select product_id, count(*) as treelevel
                  from  product_tree pt1
                  group by  product_id       )  ptl-- уровень  в иерархии
          on p.product_id = ptl.product_id
    where pt.product_id = @product_id
    order by isnull( ptl.treelevel, 0)
Выдает только имя самого элемента, который мы передали в функции, а не все имена всех его родителей..
3 июн 05, 16:47    [1595870]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Хотя если начинать этот запрос с
select @tempresult  =  @tempresult  + p.name.....
То в рекордсете содержатся все родители. Как-то неправильно запрос конкатенирует...
3 июн 05, 16:51    [1595896]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка дерева  [new]
ale-805
Member

Откуда: Питер
Сообщений: 670
Блин, как же неудобно, что тут нельзя редактировать свои сообщения. :( Ладно, продолжу разговор с самим собой:
Вернее правильно конкатенирует, она присоединяет имя последнего товара в рекордсете. А как сделать, чтобы она присоединяла ВСЕ записи рекордсета?
3 июн 05, 17:01    [1595951]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить