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

Откуда:
Сообщений: 2083
Здравствуйте! Есть иерархическая таблица примерно такого содержания
p_idtc_idtsumma
1t1_1tNULL
1t1_2t100
1_1t1_4t200
2t2_1t500
2t2_2tNULL
3tNULLt400


Надо просуммировать только по главным Parent_ID, так чтобы получилось:
p_idtSumma
1t300
2t500
3t400


Исходные данные:
declare @t table (p_id varchar(12), c_id varchar(12), summa int)
insert @t
select '1', '1_1', null union all
select '1', '1_2', 100 union all
select '1_1', '1_4', 200 union all
select '2', '2_1', 500 union all
select '2', '2_2', null union all
select '3', null, 400

select * from @t

Сообщение было отредактировано: 15 фев 12, 18:31
14 фев 12, 16:47    [12090439]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
Промахнулся с кнопкой, оформление не вставил:(
declare @t table (p_id varchar(12), c_id varchar(12), summa int)
insert @t
 select '1', '1_1', null union all
 select '1', '1_2', 100 union all
 select '1_1', '1_4', 200 union all 
 select '2', '2_1', 500 union all
 select '2', '2_2', null union all
 select '3', null, 400

select * from @t
14 фев 12, 16:47    [12090453]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
Решение можно и для SQL 2005 и 2008
14 фев 12, 16:50    [12090476]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
Программист-Любитель
Member

Откуда:
Сообщений: 16839
Где корневой '1' ?
14 фев 12, 16:52    [12090492]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
Программист-Любитель,

В том-то и дело, что в таблице нет строки типа
Parent_idChild_ID
null1
14 фев 12, 17:01    [12090582]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
Программист-Любитель
Member

Откуда:
Сообщений: 16839
Если у вас в таблице будут только значения для самых нижних листиков деревьев, без информации о том, как этот листик растет от вершины, то получить значения сумм для корневых веток в общем случае будет невозможно.
14 фев 12, 17:06    [12090630]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
Программист-Любитель,

Минут 15 назад здесь 10546216 нашел похожее решение, но прикрутить к своим данным не могу :(
14 фев 12, 17:11    [12090679]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
нет рутов
Guest
user89,

первым делом найдите все паренты у которых нет парентов, это и будут ваши руты
дальше от них рекурсией проставте всем чайлд значения парента и сгруппируйте по этому полю.
14 фев 12, 17:16    [12090717]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
нет рутов
Guest
нет рутов
user89,

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

ошибочка
14 фев 12, 17:18    [12090728]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
нет рутов,
спасибо за идею.

Додумался до такого (с более хитрыми тестовыми данными)

declare @t table (p_id varchar(12), c_id varchar(12), summa int)
insert @t select '1', '1_1', null union all select '1', '1_2', 100 union all select '1_1', '1_4', 200 union all select '2', '2_1', 500 union all
          select '2', '2_2', 50 union all select '3', null, 400 select * from @t

;with cte as (
 select t1.p_id, cast(t1.p_id as varchar(8000)) [FullPath], summa, 0 [lev] from @t t1
 where not exists (select t2.p_id from @t t2 where t1.p_id = t2.c_id)
 union all
 select t.c_id, cast(cte.FullPath +';'+ t.c_id as varchar(8000)) [FullPath], t.summa, lev+1 from @t t
 inner join cte on t.p_id = cte.p_id
), tmp as (
  select distinct left(cte.FullPath, charindex(';', cte.FullPath + ';')-1) [p_id], cte.Summa
  from cte
  where cte.p_id is not null  
)
select p_id, sum(Summa) [Summa]
from tmp
group by p_id
14 фев 12, 18:05    [12091162]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
хм-хм
Guest
user89,

если данные такие, чем не подойдет такой запрос?

select
	left(p_id,1), sum(coalesce(summa,0))
from 
	@t
group by left(p_id,1)
15 фев 12, 08:06    [12093029]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом. Рукурсия?  [new]
user89
Member

Откуда:
Сообщений: 2083
хм-хм,
нет. В реальной базе, у Parent_ID и Child_ID - тип uniqueidentifier
1, 1_2, 2_2   
я привел для удобства и простоты восприятия.

Более быстрый вариант:
declare @t table (p_id varchar(12), c_id varchar(12), summa int)
insert @t select '1', '1_1', null union all select '1', '1_2', 100 union all select '1_1', '1_4', 200 union all select '2', '2_1', 500 union all
          select '2', '2_2', 50 union all select '3', null, 400

select * from @t

;with cte as (
 select t1.p_id [c_id], t1.p_id [p_id], summa from @t t1
 where not exists (select t2.p_id from @t t2 where t1.p_id = t2.c_id)
 union all
 select t.c_id, cte.p_id, t.summa from @t t
 inner join cte on t.p_id = cte.c_id
), tmp as (
  select distinct p_id, Summa
  from cte
  where c_id is not null
)
select p_id, sum(Summa) [Summa]
from tmp
group by p_id
15 фев 12, 11:15    [12093794]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить