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

CREATE TABLE [dbo].[SimpleTree1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[parent_id] [bigint] NOT NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectPrice] [money] NULL
)

--верхний узел дерева
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (0, 'Грузовик', 0)
--листья 1-го уровня
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (1, 'Мотор',0) --у этой записи id=2
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (1, 'Кабина',0)
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (1, 'Шасси',0)
--листья второго уровня для id=2
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (2, 'Карбюратор',100)
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (2, 'Кабина',200)
INSERT INTO [dbo].[SimpleTree1] ([parent_id], [ObjectName], [ObjectPrice])
VALUES (2, 'Шасси',300)

Так вот, необходимо написать CTE, при вызове которого будет выполнен UPDATE всех веток дерева, имеющих листья, причём ObjectPrice родителя должен содержать сумму ObjectPrice детей (подветок) с самого нижнего уровня до самого верха.

Порывшись по форуму, направление я представляю (UPDATE с помощью CTE), но если поможете решить задачу быстрее - буду благодарен.
16 авг 09, 17:41    [7543705]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
vino
Member

Откуда:
Сообщений: 1191
Вопросильня
...будет выполнен UPDATE всех веток дерева, имеющих листья, причём ObjectPrice родителя должен содержать сумму ObjectPrice детей (подветок) с самого нижнего уровня до самого верха...
а могут ли в дереве быть ветки, не имеющие листья? как же распознавать те самые "листья"?
17 авг 09, 00:09    [7544195]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
Вопросильня
Guest
Наверное задачу не совсем чётко сформулировал.

Нужно, имея id листа, обновить суммы всех веток, подветкой которых является этот лист.

--
В данном случае считается, что ветка является веткой верхнего уровня, если у неё parent_id = 0.
17 авг 09, 07:24    [7544429]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
Вопросильня
Guest
Ещё одна поправка.

Вид таблицы -
CREATE TABLE [dbo].[SimpleTree1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[parent_id] [bigint] NOT NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectPrice] [money] NULL,
[Inputable] [bit] NULL
)

в столбце Inputable хранится значение true, если ветка не имеет подветок, соответственно в неё можно вводить данные и false - еслив ветка имеет подветки, соответственно данные вычисляются.

--верхний узел дерева
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (0, 'Грузовик', 0, 0)
--листья 1-го уровня
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (1, 'Мотор',0,0) --у этой записи id=2
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (1, 'Кабина',0,1)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (1, 'Шасси',0,1)
--листья второго уровня для id=2
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (2, 'Карбюратор',100,2)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (2, 'Коробка',200,1)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable])
VALUES (2, 'Насос',300,1)

Задачу хотелось предельно упростить, отсюда некоторые упущения, извиняюсь.
17 авг 09, 07:33    [7544440]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
Вопросильня
Guest
Ещё одна поправка (с форматированием).

Вид таблицы -
CREATE TABLE [dbo].[SimpleTree1](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[parent_id] [bigint] NOT NULL,
[ObjectName] [nvarchar](50) NULL,
[ObjectPrice] [money] NULL,
[Inputable] [bit] NULL
)

в столбце Inputable хранится значение true, если ветка не имеет подветок, соответственно в неё можно вводить данные и false - еслив ветка имеет подветки, соответственно данные вычисляются.

--верхний узел дерева
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (0, 'Грузовик', 0, 0)
--листья 1-го уровня
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (1, 'Мотор',0,0) --у этой записи id=2
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (1, 'Кабина',0,1)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (1, 'Шасси',0,1)
--листья второго уровня для id=2
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (2, 'Карбюратор',100,2)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (2, 'Коробка',200,1)
INSERT INTO [dbo].[SimpleTree2] ([parent_id], [ObjectName], [ObjectPrice],[Inputable]) 
VALUES (2, 'Насос',300,1)

Задачу хотелось предельно упростить, отсюда некоторые упущения, извиняюсь.
17 авг 09, 07:34    [7544442]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
vino
Member

Откуда:
Сообщений: 1191
Вопросильня, т.е. у вас точно флаг [Inputable] означает некий "лист"? Тогда выбрать лист и его предков - тривиальная задача. А вот задача обновления полей [ObjectPrice] абсолютно не поставлена, и, скорее всего, указывает на ошибку в проектировании.
Напрмиер, какое начальное значение должно быть в предках "листа"?
Если при построении дерева обеспечить изначально корректное значение [ObjectPrice] = 0, то тогда алгоритм в триггере примерно такой - сначала из в триггере всех предков "листа" вычесть старое ненулевое значение, затем добавить к тем же узлам новое значение в триггере "листа"
17 авг 09, 16:48    [7547658]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
vino
Member

Откуда:
Сообщений: 1191
Вопросильня, т.е. у вас точно флаг [Inputable] означает некий "лист"? Тогда выбрать лист и его предков - тривиальная задача. А вот задача обновления полей [ObjectPrice] абсолютно не поставлена, и, скорее всего, указывает на ошибку в проектировании.
Например, какое начальное значение должно быть в предках "листа"?
Если при построении дерева обеспечить изначально корректное значение [ObjectPrice] = 0, то тогда алгоритм в триггере примерно такой - сначала из всех предков "листа" вычесть старое ненулевое значение (пропускать этот шаг, когда INSERT или нулевое старое значение), затем добавить к тем же узлам новое значение в триггере "листа"
Т.е. нужно любые UPDATE и INSERT делать только после включения обновляющего триггера.
В нем же следует запрещать добавление новых узлов под "листья" или устанавливать "листом" узел с подчиненными.
При небольшой вложенности (менее 30) допустимо использовать рекурсию.

Если задача, все-таки только в выборке ветки с суммирование [ObjectPrice] по подчиненным на лету - это другая задача и здесь логичен CTE. Хотя по моим наблюдениям, CTE в 2005 не достаточно хорошо реализован
17 авг 09, 16:57    [7547719]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: SQL SERVER 2005 и CTE  [new]
rawman
Member

Откуда: Москва
Сообщений: 24
у меня похожая задача
в переменной
   	DECLARE @test_table table (idm int, parent int, name varchar(max), summa float, comment	varchar(max))

лежит дерево, нужно пересчитать поле summa на основе подчиненных веток

я завел еще одну переменную

   	DECLARE @tres_table table (idm int, parent int, name varchar(max), summa float, comment	varchar(max))


а потом прошелся в курсоре

	
	DECLARE @tid int, @tparent int, @tname varchar(max), @tsumm float, @tcomment varchar(max)
	DECLARE @tsum float
	
	DECLARE Con CURSOR FOR
	SELECT * FROM @test_table order by idm desc
	OPEN Con
	FETCH NEXT FROM Con INTO @tid, @tparent, @tname, @tsumm, @tcomment
	WHILE @@FETCH_STATUS = 0
	   BEGIN 
		select @tsum = sum(summa) from @tres_table where parent = @tid
		
		if (@tsum is null) insert into @tres_table values (@tid, @tparent, @tname, @tsumm, @tcomment)
		else insert into @tres_table values (@tid, @tparent, @tname, @tsum, @tcomment)
		
	    FETCH NEXT FROM Con INTO @tid, @tparent, @tname, @tsumm, @tcomment
	   END
	CLOSE Con
	DEALLOCATE Con

	select * from @tres_table order by idm asc


но таблица должна начинаться с листьев и стремиться к вершинам дерева, для этого я отсортировал
by idm desc
11 янв 12, 14:25    [11885318]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
-CTE-
Guest
rawman



insert @test_table(idm, parent, name, summa)
select 2, 0, '1', null
union all
select 1, 2, '1-1', 1
11 янв 12, 18:47    [11887862]     Ответить | Цитировать Сообщить модератору
 Re: SQL SERVER 2005 и CTE  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
rawman
у меня похожая задача
Это был вопрос или ответ???
11 янв 12, 19:54    [11888324]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить