Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / сортировка дерева / 7 сообщений из 7, страница 1 из 1
21.07.2018, 00:52
    #39677172
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
Господа доброго времени.

в пятницу на работе рисовал одно задание, смысл которого иерархическая структура элементов.
на входе имеем массив элементов
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
<item id="1">
   <item id="2" parent="1"/>
   <item id="3" parent="1">
      <item id="5" parent="3"/>
      <item id="6" parent="3"/>
   </item>
</item>
<item id="20">
    <item id="21" parent="20"/>
    <item id="22" parent="20">
       <item id="23" parent="22"/>
          <item id="24" parent="23"/>
       </item>
    </item>
</item>



необходимо было представить в виде таблицы:
idnamelvl1root12test11.13test21.24test41.2.15test51.2.220root2221test212.122test222.223test232.2.124test242.2.1.1

думаю смысл понятен, собственно в реляционном виде реализация выглядит так (не по той модели что я привел выше но смысл тот же):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
declare @t table (
val char,
id int,
parent int
)
insert into @t values ('a', 1, 0), ('b', 2, 1), ('c', 3, 1), ('d', 4, 2), ('e', 5, 2), ('f', 6, 3);
insert into @t values ('i', 7, 0), ('j', 8, 7), ('k', 9, 7), ('l', 10, 9), ('m', 11, 9), ('x', 12, 8);
--select * from @t;

with rcte as (
select val,
       id,
       parent,
       1 as [lvl],
       convert(varchar, row_number() over (order by id)) as [lvl_descr]
from @t
where [parent] = 0
union all
select
      t.val,
      t.id,
      t.parent,
      rcte.[lvl]+1 as [lvl], 
      convert(varchar, rcte.[lvl_descr]+'.'+convert(varchar, row_number() over (partition by t.[parent] order by t.[id]))) as [lvl_descr]
from rcte
    join @t t on t.[parent] = rcte.id
)
select * from rcte 
order by convert(binary(16), lvl_descr) option(maxrecursion 0);
--order by convert(int, left(replace(lvl_descr, '.', '')+'000000', 6)) option(maxrecursion 0);



но вот эта сортировка в конце меня как то напрягает, есть у кого мысли как можно элегантнее нарисовать?

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

это вообщем пятничный вопрос: а можно лучше?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 10:58
    #39677547
court
court
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
felix_ff,

а "о чём" вообще эта сортировка ? :)
Зачем этот convert в binary ? чем сортировка по convert-у lvl_descr отличается от сортировки просто по lvl_descr ?

Вы заметили, что на таком,
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
;with cte as (
	select lvl_descr from 
	(values 
		 ('2')
		,('2.1')
		,('2.2')
		,('2.2.1')
		,('2.2.1.1')
		,('2.2.1.10')
		,('2.2.1.2')
		) as T(lvl_descr))
select * from cte order by convert(binary(16), lvl_descr)


будет "лажать" и с "конвертом" и без
lvl_descr22.12.22.2.12.2.1.12.2.1.102.2.1.2

------------------------
как-то так можно, в принципе, решить
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
declare @x xml =
'<item id="1">
   <item id="2" parent="1"/>
   <item id="3" parent="1">
      <item id="5" parent="3"/>
      <item id="6" parent="3"/>
      <item id="7" parent="3"/>
      <item id="8" parent="3"/>
      <item id="9" parent="3"/>
      <item id="10" parent="3"/>
      <item id="11" parent="3"/>
      <item id="12" parent="3"/>
      <item id="13" parent="3"/>
      <item id="14" parent="3"/>
      <item id="15" parent="3"/>
   </item>
</item>
<item id="20">
    <item id="21" parent="20"/>
    <item id="22" parent="20">
       <item id="23" parent="22">
          <item id="24" parent="23"/>
       </item>
    </item>
</item>'

;with cte as (
select 
	id		=t.c.value('@id','int')
	,parent =t.c.value('@parent','int')
	,x		=t.c.query('item')
	,lvl_descr	=cast(row_number()over(partition by t.c.value('@parent','int') order by t.c.value('@id','int')) as varchar(max))
	,lvl	=1	 
	,orderby=cast(1.0*row_number()over(partition by t.c.value('@parent','int') order by t.c.value('@id','int')) as float)
from @x.nodes('item') as t(c)

union all

select
	id		=a.id
	,parent =a.parent
	,x		=a.x
	,lvl_descr	=a.lvl_descr
	,lvl	=cte.lvl+1 
	,orderby=a.orderby 
from cte
cross apply (select  
				id		=t.c.value('@id','int')
				,parent =t.c.value('@parent','int')
				,x		=t.c.query('item')
				,lvl_descr	=cte.lvl_descr+'.'+cast(row_number()over(partition by t.c.value('@parent','int') order by t.c.value('@id','int')) as varchar(max))
				,orderby=cast(cte.orderby+(row_number()over(partition by t.c.value('@parent','int') order by t.c.value('@id','int'))/power(100.0,cte.lvl)) as float)
			from x.nodes('item') as t(c)) a

)
select 
		id		
		,parent
		,lvl_descr 
		,lvl	
		,orderby
from cte 
order by orderby
option(maxrecursion 0);


idparentlvl_descrlvlorderby1NULL111211.121,01311.221,02531.2.131,0201631.2.231,0202731.2.331,0203831.2.431,0204931.2.531,02051031.2.631,02061131.2.731,02071231.2.831,02081331.2.931,02091431.2.1031,0211531.2.1131,021120NULL21221202.122,0122202.222,0223222.2.132,020124232.2.1.142,020101
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 11:56
    #39677584
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
court,

Опачки, да. я что то до десятки в тестовом варианте не добежал, мой вариант - ни о чем, согласен.

с xml можно было не заморачиваться просто она наглядней структуру отражает, но все равно спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 12:13
    #39677609
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
felix_ffа можно лучше?Не знаю критериев "лучшести", но еще можно, например, так:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
declare @x xml = N'<item id="1">
   <item id="2" parent="1"/>
   <item id="3" parent="1">
      <item id="5" parent="3"/>
      <item id="6" parent="3"/>
      <item id="7" parent="3"/>
      <item id="8" parent="3"/>
      <item id="9" parent="3"/>
      <item id="10" parent="3"/>
      <item id="11" parent="3"/>
      <item id="12" parent="3"/>
      <item id="13" parent="3"/>
      <item id="14" parent="3"/>
      <item id="15" parent="3"/>
   </item>
</item>
<item id="20">
    <item id="21" parent="20"/>
    <item id="22" parent="20">
       <item id="23" parent="22">
          <item id="24" parent="23"/>
       </item>
    </item>
</item>';

with a as
(
 select
  row_number() over (order by (select 1)) as rn,
  t.n.value('@id', 'int') as id,
  t.n.value('@parent', 'int') as parent
 from
  @x.nodes('//item') t(n)
),
b as
(
 select
  id, parent, cast('/' + cast(row_number() over (order by rn) as varchar(10)) + '/' as hierarchyid) as h
 from
  a
 where
  parent is null

 union all

 select
  a.id, a.parent, cast(b.h.ToString() + cast(row_number() over (order by a.rn) as varchar(10)) + '/' as hierarchyid) as h
 from
  b join
  a on a.parent = b.id
)
select
 id, parent, h.ToString()
from
 b
order by
 h;

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 12:27
    #39677630
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
invm,

тоже подходит.

в моем понимании "критерий лучшести" был бы выведением колонки сортировки дерева типа int/float с минимальными плясками с бубнами.

id name lvl_descr order 1 root 1 12 test1 1.1 23 test2 1.2 34 test4 1.2.1 45 test5 1.2.2 520 root2 2621 test21 2.1 722 test22 2.2823 test23 2.2.1924 test24 2.2.1.110
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 13:38
    #39677696
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
Да, решение с hierarсhyid выглядит вроде идеальным.
 

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
declare @t table (
  val char,
  id int,
  parent int
);
insert into @t values ('a', 1, 0), ('b', 2, 1), ('c', 3, 1), ('d', 4, 2), ('e', 5, 2), ('f', 6, 3);
insert into @t values ('i', 7, 0), ('j', 8, 7), ('k', 9, 7), ('l', 10, 9), ('m', 11, 9), ('x', 12, 8);
insert into @t values ('0', 50, 2), ('1', 51, 2), ('2', 52, 2), ('3', 53, 2), ('4', 54, 2), ('5', 55, 2), ('6', 56, 2), ('7', 57, 2), ('8', 58, 2), ('9', 59, 2);
insert into @t values ('0', 60, 3), ('1', 61, 3), ('2', 62, 3), ('3', 63, 3), ('4', 64, 3);

WITH rcte as (
    SELECT [val],
           [id],
           [parent],
           CAST('/' + CAST(ROW_NUMBER() OVER (ORDER BY [id]) AS VARCHAR) + '/' AS HIERARCHYID) AS [order]
    FROM @t
    WHERE [parent] = 0
    UNION ALL
    SELECT
          t.val,
          t.id,
          t.parent,
          CAST(rcte.[order].ToString() + CAST(ROW_NUMBER() OVER (PARTITION BY t.[parent] ORDER BY t.[id]) AS VARCHAR) + '/' AS HIERARCHYID) as [order]
    FROM rcte
        JOIN @t t ON t.[parent] = rcte.id
)
select [val], [id], [parent], [order].GetLevel() AS [lvl], stuff(replace(left([order].ToString(), len([order].ToString())-1), '/', '.'), 1, 1, '') as [order]--, [order].ToString() as [x], [order] as y
from rcte 
order by [order]
--order by convert(hierarchyid, [order])
option(maxrecursion 0);




я правда не очень понял почему в конце в order by нужно опять делать приведение к hierarchyid, без конверта он сортирует как строку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
23.07.2018, 13:47
    #39677704
felix_ff
felix_ff
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
сортировка дерева
ааа все догнал, я в результирующем наборе алиас вогнал который строку дает :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / сортировка дерева / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]