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

create table test_tree(
	[id] int not null
	, [level] int not null
	, [parent] int not null
	, [name] nvarchar(30)
	constraint pk_id_level_parent
		primary key clustered ([level], [parent], [id])
	) on [primary]

create nonclustered index idx_name 
	on dbo.test_tree([name])


insert into dbo.test_tree ([level], [parent], [id], [name])
	select 1, 0, 1, N'A1' union all
	select 2, 1, 3, N'B1' union all
	select 2, 1, 1, N'B2' union all
	select 2, 1, 2, N'B3' union all
	select 3, 2, 1, N'C1' union all
	select 3, 2, 2, N'C3' union all
	select 3, 3, 3, N'C2' union all
	select 3, 3, 4, N'C4' union all
	select 4, 1, 4, N'D1' union all
	select 4, 1, 1, N'D2' union all
	select 4, 1, 2, N'D3' union all
	select 4, 3, 3, N'D4' union all
	select 5, 4, 1, N'E1' union all
	select 5, 1, 4, N'E2' union all
	select 5, 2, 2, N'E3' union all
	select 5, 2, 3, N'E4' union all
	select 5, 2, 5, N'E5' union all
	select 5, 2, 6, N'E6' union all
	select 5, 2, 7, N'E7' union all
	select 5, 4, 8, N'E8' union all
	select 6, 5, 1, N'F1'


;with tree([id], [parent], [level], [name])
	as (
		select [id], [parent], [level], [name] 
			from test_tree
			where [parent] = 0
		union all
		select t.[id], t.[parent], t.[level], t.[name]
			from test_tree t
			inner join tree i on t.[parent] = i.id
	)
select * from tree


Результат:

id parent level name
1 0 1 A1
1 1 2 B2
2 1 2 B3
3 1 2 B1
1 1 4 D2
2 1 4 D3
4 1 4 D1
4 1 5 E2
1 4 5 E1
8 4 5 E8
1 1 2 B2
2 1 2 B3
3 1 2 B1
.......
и сообщение об ошибке:
Сообщение 530, уровень 16, состояние 1, строка 4
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

А вторая часть вопроса по индексам. Правильно ли я их сделала?
17 июн 13, 13:10    [14441722]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
Подскажите, пожалуйста, что я не так делаю?

G. Using MAXRECURSION to cancel a statement
MAXRECURSION can be used to prevent a poorly formed recursive CTE from entering into an infinite loop.

Лина1995
А вторая часть вопроса по индексам. Правильно ли я их сделала?

Правильно для данного единственного запроса ?
17 июн 13, 13:12    [14441741]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
B2 сам себе папа
17 июн 13, 13:15    [14441765]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Подскажите, пожалуйста, как посмотреть все дерево от определенного элемента?

Подскажите, пожалуйста, как правильно сделать индексы, первичные ключи для данной таблицы с целью максимальной производительности при условии, что в основном будет только чтение из этой таблицы, в основном она будет рости в ширину, основная часть запросов будет либо поиск по полю [name], либо просмотр дерева от определенного элемента.
17 июн 13, 13:20    [14441802]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Паганель
B2 сам себе папа

Ну почему-же?
Он на втором уровне и его родитель с id = 1, просто у него нет потомков. Вроде так.
17 июн 13, 13:22    [14441817]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22550
Лина1995
Паганель
B2 сам себе папа

Ну почему-же?
Он на втором уровне и его родитель с id = 1, просто у него нет потомков. Вроде так.
я в Вашем join-е вижу только условие на папу
никаких условий, учитывающих уровень
17 июн 13, 13:24    [14441830]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
Подскажите, пожалуйста, как посмотреть все дерево от определенного элемента?

Глазами можно.
Можно запрос написать.
Главно понимать, что собственно понимать под "посмотреть" и "от элемента"

Лина1995
часть запросов будет либо поиск по полю [name]

Вида [name] like '%Вася%' ?
17 июн 13, 13:26    [14441849]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Glory
Лина1995
часть запросов будет либо поиск по полю [name]

Вида [name] like '%Вася%' ?

нет, вида: [name]=N'Вася'
17 июн 13, 13:29    [14441865]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
нет, вида: [name]=N'Вася'

И сколько записей с N'Вася' планируется ? А сколько с другими именами ?
И какие поля еще будут выбираться ?
17 июн 13, 13:31    [14441878]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Glory
Лина1995
нет, вида: [name]=N'Вася'

И сколько записей с N'Вася' планируется ? А сколько с другими именами ?
И какие поля еще будут выбираться ?

Другие поля если и будут, то в поиске участвовать не будут ТОЧНО.
Вась может быть много, как и Петь и т.д.
17 июн 13, 13:38    [14441913]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
Другие поля если и будут, то в поиске участвовать не будут ТОЧНО.

Вопрос был не про поиск, а про выборку
17 июн 13, 13:39    [14441920]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Glory
Лина1995
Подскажите, пожалуйста, как посмотреть все дерево от определенного элемента?

Глазами можно.
Можно запрос написать.
Главно понимать, что собственно понимать под "посмотреть" и "от элемента"

В идеале хотелось бы получить такого вида:

TREE
-----------------------------
Россия
Воронеж
ООО "Рога и копыта"
Главный офис
Офис 1
Офис 2
Сервер 1
Лиски
ЛискиПресс
Москва
тут смотрела
Но не получилось.
17 июн 13, 13:42    [14441934]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Glory
Лина1995
Другие поля если и будут, то в поиске участвовать не будут ТОЧНО.

Вопрос был не про поиск, а про выборку

Хорошо. Другие поля не будут участвовать в выборке.
17 июн 13, 13:43    [14441942]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
тут смотрела
Но не получилось.

Не получилось запустить пример из ссылки ?
Или не получилось добавить в свою таблицу правильные данные ?
И причем тут Ораклавский синтакис ?
17 июн 13, 13:45    [14441956]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Лина1995
Хорошо. Другие поля не будут участвовать в выборке.

для запроса
select name from mytable where name = N'Вася'
индекс
create nonclustered index idx_name on dbo.test_tree([name])
подходит замечательно
17 июн 13, 13:47    [14441963]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
with
test_tree as (select * from (values
   (1, 0, 1, N'A1'),
   (2, 1, 3, N'B1'),(2, 1, 1, N'B2'),(2, 1, 2, N'B3'),
   (3, 2, 1, N'C1'),(3, 2, 2, N'C3'),(3, 3, 3, N'C2'),(3, 3, 4, N'C4'),
   (4, 1, 4, N'D1'),(4, 1, 1, N'D2'),(4, 1, 2, N'D3'),(4, 3, 3, N'D4'),
   (5, 4, 1, N'E1'),(5, 1, 4, N'E2'),(5, 2, 2, N'E3'),(5, 2, 3, N'E4'),(5, 2, 5, N'E5'),(5, 2, 6, N'E6'),(5, 2, 7, N'E7'),
   (5, 4, 8, N'E8'),(6, 5, 1, N'F1')
   )tmp([level], [parent], [id], [name])),
tree as (
   select level, id, parent, name 
      from test_tree 
      where level=1
   union all
   select t.level, t.id, t.parent, t.name
      from tree r
      join test_tree t on t.level=r.level+1 and t.parent=r.id 
   )
select * from tree
order by level, id, parent
17 июн 13, 13:47    [14441969]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
AnaceH
Member

Откуда:
Сообщений: 109
Лина1995

TREE
-----------------------------
Россия
Воронеж
ООО "Рога и копыта"
Главный офис
Офис 1
Офис 2
Сервер 1
Лиски
ЛискиПресс
Москва

А почему вы храните совершенно разнородные сущности в одной таблице, еще и в виде иерархии? Почему бы не выделить соответствующие сущности? Если так хочется строить подобные иерархии, используйте MongoDB, например. Я не хочу сказать, что в sql сервере нельзя хранить иерархии, но по-моему именно в данном случае это совсем неуместно.
17 июн 13, 13:56    [14442047]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Ujcnm_12345
Guest
;with tree([id], [parent], [level], [name], [sc])
	as (
		select [id], [parent], [level], [name], CAST(id AS VARCHAR(100)) AS sc
			from test_tree
			where [parent] = 0
		union all
		select t.[id], t.[parent], t.[level], t.[name], CAST(i.sc + '_' + CAST(t.id AS VARCHAR) AS VARCHAR(100))
			from test_tree t
			inner join tree i on t.[parent] = i.id AND t.level = i.level + 1
	)
select * from tree ORDER BY sc
17 июн 13, 14:00    [14442089]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Cygapb-007
with
test_tree as (select * from (values
   (1, 0, 1, N'A1'),
   (2, 1, 3, N'B1'),(2, 1, 1, N'B2'),(2, 1, 2, N'B3'),
   (3, 2, 1, N'C1'),(3, 2, 2, N'C3'),(3, 3, 3, N'C2'),(3, 3, 4, N'C4'),
   (4, 1, 4, N'D1'),(4, 1, 1, N'D2'),(4, 1, 2, N'D3'),(4, 3, 3, N'D4'),
   (5, 4, 1, N'E1'),(5, 1, 4, N'E2'),(5, 2, 2, N'E3'),(5, 2, 3, N'E4'),(5, 2, 5, N'E5'),(5, 2, 6, N'E6'),(5, 2, 7, N'E7'),
   (5, 4, 8, N'E8'),(6, 5, 1, N'F1')
   )tmp([level], [parent], [id], [name])),
tree as (
   select level, id, parent, name 
      from test_tree 
      where level=1
   union all
   select t.level, t.id, t.parent, t.name
      from tree r
      join test_tree t on t.level=r.level+1 and t.parent=r.id 
   )
select * from tree
order by level, id, parent

Простите, возможно я не поняла, но вроде что-то подобное делала, но результат совсем не тот.
Вот так у меня было
;with tree([id], [parent], [level], [name])
	as (
		select [id], [parent], [level], [name]
			from test_tree
			where [parent] = 0
		union all
		select t.[id], t.[parent], t.[level], t.[name]
			from tree i
			inner join test_tree t on t.[parent] = i.id and t.[level] = i.[level] + 1
	)
select * from tree
17 июн 13, 14:12    [14442183]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
точнее,
with tree as (
   select level, id, parent, name, convert(varchar(100),id) [path]
      from test_tree 
      where level=1
   union all
   select t.level, t.id, t.parent, t.name, convert(varchar(100),[path]+'.' + convert(varchar(100),t.id))
      from tree r
      join test_tree t on t.level=r.level+1 and t.parent=r.id 
   )
select SPACE((level-1)*5)+name tree_name
from tree
order by path
17 июн 13, 14:13    [14442189]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Ujcnm_12345
;with tree([id], [parent], [level], [name], [sc])
	as (
		select [id], [parent], [level], [name], CAST(id AS VARCHAR(100)) AS sc
			from test_tree
			where [parent] = 0
		union all
		select t.[id], t.[parent], t.[level], t.[name], CAST(i.sc + '_' + CAST(t.id AS VARCHAR) AS VARCHAR(100))
			from test_tree t
			inner join tree i on t.[parent] = i.id AND t.level = i.level + 1
	)
select * from tree ORDER BY sc

Супер! Это уже ближе к тому, что я хотела
17 июн 13, 14:14    [14442198]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
Cygapb-007
точнее,
with tree as (
   select level, id, parent, name, convert(varchar(100),id) [path]
      from test_tree 
      where level=1
   union all
   select t.level, t.id, t.parent, t.name, convert(varchar(100),[path]+'.' + convert(varchar(100),t.id))
      from tree r
      join test_tree t on t.level=r.level+1 and t.parent=r.id 
   )
select SPACE((level-1)*5)+name tree_name
from tree
order by path

Вообще замечательно! Спасибо! ;-*
Буду вникать!
17 июн 13, 14:16    [14442218]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Лина1995
Guest
А подскажите еще, пожалуйста, может где-то есть заготовки по функциям/процедурам работы с такими древовидными структурами?
ms sql скорее всего 2005, а может и вообще 2000 будет.
А может и статьи какие подскажите? но без hierarchyid.

И еще раз по индексам.
Правильно ли я сделала, что
primary key clustered ([level], [parent], [id])
? Порядок полей в этом списке тоже важен?
17 июн 13, 14:26    [14442296]     Ответить | Цитировать Сообщить модератору
 Re: древовидные запросы  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Лина1995
А подскажите еще, пожалуйста, может где-то есть заготовки по функциям/процедурам работы с такими древовидными структурами?
ms sql скорее всего 2005, а может и вообще 2000 будет.
А может и статьи какие подскажите? но без hierarchyid.

И еще раз по индексам.
Правильно ли я сделала, что
primary key clustered ([level], [parent], [id])
? Порядок полей в этом списке тоже важен?

Конечно важен...
лучше id,parent,level
17 июн 13, 14:32    [14442328]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить