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

Откуда:
Сообщений: 372
Есть таблица:
ID, Parent_ID, Value
В узлах сумма не совпадает с суммой дочерних элементов.
Для этого добавляю в каждом узле новый дочерний элемент и разницу списываю в него.

Вопрос:
Как написать запрос, который будет в иерархичном списке производить описанные выше действия?

Те, кто заносят так данные и высылают мне, считают, что все дочерние элементы - это "в том числе". Поэтому сумма дочерних ваще не обязательно равна значению в узле.
Ну, типа:
1. Бюджетирую затраты в поездке на сумму 1000.
1.1. В том числе на экскурсии 400
1.1.1 В том числе на экскурсию к памятнику 150
1.2. В том числе на жрачку 300.
Всё.

Я планирую преобразовать это в:
1. Бюджетирую затраты в поездке на сумму 1000.
1.1. В том числе на экскурсии 400
1.1.1 В том числе на экскурсию к памятнику 150
1.1.2. Другое - 400-150=250
1.2. В том числе на жрачку 350.
1.3. Другое - 1000-400-350=250

Исходные данные:
Таблица пунктов
ID, Parent_ID, Name
и таблица планируемых затрат:
Пункт_ID, Денежная сумма

К сообщению приложен файл. Размер - 103Kb
27 окт 19, 17:40    [22003687]     Ответить | Цитировать Сообщить модератору
 Re: Заковыристый рекурсивный запрос: выделить лишнее из узлов  [new]
хорошо я согласен
Member

Откуда:
Сообщений: 372
уточню: нужно сформировать новую таблицу, а не UPDATE в текущей.
27 окт 19, 17:51    [22003692]     Ответить | Цитировать Сообщить модератору
 Re: Заковыристый рекурсивный запрос: выделить лишнее из узлов  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
хорошо я согласен,

ID-шники дочерние мне неохота вам рисовать, там много парсинга, скорее всего у вас там все таки ID и можно просто IDENTITY использовать.

SELECT ID, Parent_ID, Value
FROM [table]
UNION ALL
SELECT ID + '.999', t.ID, t.Value - SUM(t_ch.Value)
FROM [table] t
        INNER  JOIN [table] t_ch ON t.Id = t_ch.Parent_ID
GROUP BY t.ID
HAVING SUM(t_ch.Value) < t.Value
28 окт 19, 11:28    [22004023]     Ответить | Цитировать Сообщить модератору
 Re: Заковыристый рекурсивный запрос: выделить лишнее из узлов  [new]
court
Member

Откуда:
Сообщений: 1956
declare @t1 table(ID int, Parent_ID int, Name varchar(50))
insert into @t1 values
(1, null, 'Бюджетирую затраты в поездке'), (2,1,'экскурсии'),(3,2,'экскурсию к памятнику'),(4,1,'жрачку')

declare @t2 table(t1ID int, TotalSum money)
insert into @t2 values
(1,1000),(2,400),(3,150),(4,300)

;with cte as (
	select 
		t1.ID
		,t1.Parent_ID
		,t1.Name
		,t2.TotalSum
		,tt2.TotalSum as Parent_TotalSum 
		,sum(t2.TotalSum)over(partition by t1.Parent_ID) as Level_TotalSum
		,row_number()over(partition by t1.Parent_ID order by t1.ID) as Level_num
		,max(t1.ID)over(partition by t1.Parent_ID) as Max_Level_ID
		,count(t1.ID)over(partition by t1.Parent_ID) as Level_ID_count
	from @t1 t1 inner join @t2 t2 on t1.ID = t2.t1ID
	left join @t2 tt2 on t1.Parent_ID = tt2.t1ID
),
cte1 as (
	select 
		*
		,Num		=cast(Level_num as varchar(max))
		,ParentNum	=cast('' as varchar(max)) 
	from cte
	where Parent_ID is null

	union all

	select 
		cte.*
		,Num		=cte1.Num+'.'+cast(cte.Level_num as varchar(max))
		,ParentNum	=cte1.Num
	from cte inner join cte1 on cte.Parent_ID = cte1.ID	
)

select
	ID
	,Parent_ID
	,Num
	,Name
	,TotalSum
from cte1 

union all

select
	null
	,Parent_ID
	,ParentNum+'.'+cast(Level_ID_count+1 as varchar)
	,Name		='Другое'
	,TotalSum	=Parent_TotalSum - Level_TotalSum
from cte1 
where	Max_Level_ID = ID
	and Parent_TotalSum - Level_TotalSum <> 0

order by 3


IDParent_IDNumNameTotalSum
1NULL1Бюджетирую затраты в поездке1000,00
211.1экскурсии400,00
321.1.1экскурсию к памятнику150,00
NULL21.1.2Другое250,00
411.2жрачку300,00
NULL11.3Другое300,00
28 окт 19, 12:09    [22004085]     Ответить | Цитировать Сообщить модератору
 Re: Заковыристый рекурсивный запрос: выделить лишнее из узлов  [new]
хорошо я согласен
Member

Откуда:
Сообщений: 372
офигеть, круто!! спасибо!!
28 окт 19, 18:10    [22004507]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить