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

Откуда:
Сообщений: 204
Доброго времени суток!
;with result as 
			(
				SELECT	row_id					,
						nakl_id					,
						pos_id					,
						pos_data				,
						nakl_type				,
						tovar					,
						region					,
						kol_pr					,
						price_old				,
						nas_stav				,
						old_pos_id				,
						convert(money, price_new			) price_new			,
						reg_quan			,
						convert(money, reg_price			) reg_price			,
						reg_price_nas
				FROM	@temp
				WHERE	row_id = 1
				UNION ALL
				SELECT	t.row_id				,
						t.nakl_id				,
						t.pos_id				,
						t.pos_data				,
						t.nakl_type				,
						t.tovar					,
						t.region				,
						t.kol_pr				,
						t.price_old				,
						t.nas_stav				,
						t.old_pos_id			,
						convert(money, isnull(pn.price_new, isnull(rp.reg_price,0))) price_new		,
						t.reg_quan				,
						convert(money, isnull(rp.reg_price, 0) ) ,
						t.reg_price_nas
			   from @temp t
				 join result r on      r.row_id+1 =	iif(t.nakl_type='prixod_otk', r.row_id+1, t.row_id)
		AND r.region		=	iif(t.nakl_type='prixod_prm', r.region, t.region)
		 AND r.tovar		=	t.tovar
		 AND r.pos_id		=	iif(t.nakl_type='prixod_otk', t.old_pos_id, iif(t.nakl_type='prixod_prm', t.pos_id, r.pos_id) )
		 AND r.nakl_type	        =	iif(t.nakl_type='prixod_prm','rasxod_prm',r.nakl_type)

				cross apply
				(
						SELECT
						CASE WHEN t.nakl_type='prixod_inv' THEN iif(t.reg_quan-t.kol_pr>0, r.reg_price, 0)
						ELSE 
							CASE WHEN t.nakl_type='prixod_otk' THEN  r.reg_price
							ELSE 
								CASE WHEN t.nakl_type='prixod_kup' THEN t.price_old
								ELSE 
									CASE WHEN t.nakl_type='prixod_prm' THEN  r.reg_price
									ELSE 
										CASE WHEN t.nakl_type='rasxod_vp' THEN  t.price_old
										ELSE 
											CASE WHEN t.nakl_type='rasxod_zvk' THEN  r.reg_price
											ELSE 
												r.reg_price
											END 
										END 
									END 
								END 
							END 
						END as price_new					
				) pn
				cross apply
				(
					SELECT
						CASE WHEN 
							t.reg_quan>0
						THEN
							((t.reg_quan + iif(t.nakl_type in('rasxod_vp', 'rasxod_zvk','rasxod_prm'),1,-1)*isnull(t.kol_pr,0))*r.reg_price +iif(t.nakl_type in('rasxod_vp', 'rasxod_zvk','rasxod_prm'),-1,1)*isnull(t.kol_pr,0)*isnull(pn.price_new,0) ) / t.reg_quan
						ELSE 
							convert(money, r.reg_price)
						END
					as reg_price
				) rp
			)
			UPDATE t
				SET t.price_new = r.price_new,
					t.reg_quan = r.reg_quan,
					t.reg_price = r.reg_price,
					t.reg_price_nas = r.reg_price_nas
			FROM @temp t, result r
			WHERE t.row_id=r.row_id AND t.pos_id=r.pos_id AND  t.nakl_type=r.nakl_type and t.region=r.region and t.tovar=r.tovar
			option(maxrecursion 0);


Вычисляется reg_price (актуальная цена товара) через CTE, Дело в том что чтобы посчитать reg_price, мне нужно получить price_new (а это берется из CTE result r и в разных случаях по разной привязки таблиц). Сделать двойной join внутри CTE не разрешено. Делаю через условный JOIN по типу накладной,
 join result r on      r.row_id+1 =	iif(t.nakl_type='prixod_otk', r.row_id+1, t.row_id)
		AND r.region		=	iif(t.nakl_type='prixod_prm', r.region, t.region)
		 AND r.tovar		=	t.tovar
		 AND r.pos_id		=	iif(t.nakl_type='prixod_otk', t.old_pos_id, iif(t.nakl_type='prixod_prm', t.pos_id, r.pos_id) )
		 AND r.nakl_type	        =	iif(t.nakl_type='prixod_prm','rasxod_prm',r.nakl_type)


Цена считается нормально до определенных записей, до тех пор не изменились условия JOIN (т.е., например текущий тип накладной prixod_prm а на этот тип у меня другие условия join)

Как быть? Мне нужно посчитать актуальную цену для каждой записи в СТЕ, но каждый раз я должен получить предыдущую актуальную цену из СТЕ по разным привязкам.
7 июл 15, 09:03    [17860330]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
max aka max, выбирайте во временную таблицу и делайте с ней что хотите.
7 июл 15, 11:15    [17861011]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
max aka max, выбирайте во временную таблицу и делайте с ней что хотите.

Не могу, так как каждая запись зависит от предыдущей записи, если один раз взять в темп, там данные же не изменятся.
7 июл 15, 12:32    [17861671]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
max aka max,

не совсем понято, СТЕ не решает проблему зависимости записей. Последовательно произвести вычисления можно только курсором. (Если Вы полагаете, что приведенный запрос говорит сам за себя, то это не так).
7 июл 15, 12:55    [17861905]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
max aka max,

не совсем понято, СТЕ не решает проблему зависимости записей. Последовательно произвести вычисления можно только курсором. (Если Вы полагаете, что приведенный запрос говорит сам за себя, то это не так).


Из-за того что курсор работал медленно, я был вынужден искать решение в СТЕ (
7 июл 15, 12:58    [17861925]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Glory
Member

Откуда:
Сообщений: 104760
max aka max
Из-за того что курсор работал медленно

Не курсор у вас медленно работает, а куча команд между двумя итерациями.
А вы почему то решили, что один монструозный запрос CTE решит все проблемы.
7 июл 15, 13:00    [17861946]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
Glory
max aka max
Из-за того что курсор работал медленно

Не курсор у вас медленно работает, а куча команд между двумя итерациями.
А вы почему то решили, что один монструозный запрос CTE решит все проблемы.


Вышеприведенное использование СТЕ решает уже проблему, НО не дает для одной записи из разных строк СТЕ таблицы взять данные, (рекурсивное использование запрещается), может есть какой нибудь выход кроме как использовать курсоров ?
7 июл 15, 13:04    [17861981]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Glory
Member

Откуда:
Сообщений: 104760
max aka max
решает уже проблему, НО не дает для одной записи из разных строк

Ага, решает проблему, но дает неправильный результат. Феерично.
7 июл 15, 13:05    [17861990]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Glory
Member

Откуда:
Сообщений: 104760
max aka max
может есть какой нибудь выход

Давайте вы просто измените нужный вам результат на тот, который дает запрос.
7 июл 15, 13:06    [17862003]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
Glory
max aka max
решает уже проблему, НО не дает для одной записи из разных строк

Ага, решает проблему, но дает неправильный результат. Феерично.


неправильный результат только в двух случаях: когда мне нужно исходную цену получить из другой строки СТЕ, а актуальную цену из другой. а в остальных случаях работает.
7 июл 15, 13:07    [17862010]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Glory
Member

Откуда:
Сообщений: 104760
max aka max
неправильный результат только в двух случаях: когда мне нужно исходную цену получить из другой строки СТЕ, а актуальную цену из другой. а в остальных случаях работает.

Тогда вообще все замечательно. Исключите из ваших исходных данных эти два случая и смело внедряйте запрос
7 июл 15, 13:08    [17862026]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
_djХомяГ
Guest
max aka max
НО не дает для одной записи из разных строк СТЕ таблицы взять данные, (рекурсивное использование запрещается), может есть какой нибудь выход кроме как использовать курсоров ?

Ну можно обернуть CTE в CTE
то есть
;with result as <много букфффф>
,
result1 as <много букфф from result> ----ссылка на предыдущее cte 

,
resultN as <много букфф from resultN-1> ----ссылка на предыдущее cte 

Если это поможет и еще больше не затормозит процесс
7 июл 15, 13:09    [17862032]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
_djХомяГ, я гарантирую что автор через полгода не поймет - что же он там написал? :)
7 июл 15, 13:26    [17862165]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
Владислав Колосов
_djХомяГ, я гарантирую что автор через полгода не поймет - что же он там написал? :)


я понимаю, только вот не уверен будут ли данные которые посчитаны внутри первого блока CTE актуальны внутри второго блока
7 июл 15, 13:27    [17862174]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
_djХомяГ
Guest
Зависит от реализации
А так что посчитали на предыдущем шаге всегда будет доступно в текущем (главное не забывать включать нужные поля)
Попробуйте на каком нибудь простом тесте поэксперементировать
7 июл 15, 13:42    [17862291]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
_djХомяГ
Guest
Ну или как сказали выше, кидайте промеж расчеты во времянку/табличную переменнную и далее обрабатывайте эти данные Возможно это будет даже быстрее, чем монстроидальное CTE
7 июл 15, 13:45    [17862318]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
o-o
Guest
max aka max,

а вы можете показать, что надо получить,
не на своем примере, а на левой таблице с данными в 10 строчек?

а то как вижу код на страницу, уже лень вникать, а на говноёлки у меня вообще аллергия.
недавно мне тоже подсунули ровно такую же, только там уже вложенность case-oв была 10,
а потребовалось вписать 11-ый case, на это сервер выдает
Msg 125, Level 15, State 4, Line 51
Case expressions may only be nested to level 10.

вы в курсе?
автора ёлки уже не сыскать, а очень хотелось спросить:
чем не устраивает вот такая запись:
select case when t.nakl_type = 'prixod_inv' then iif(t.reg_quan-t.kol_pr>0, r.reg_price, 0)
	    when t.nakl_type in ('prixod_kup', 'rasxod_vp') then  t.price_old
	    else r.reg_price                      
        end as price_new
from t;

экономия места и нервов окружающих
7 июл 15, 14:01    [17862473]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
o-o
max aka max,

а вы можете показать, что надо получить,
не на своем примере, а на левой таблице с данными в 10 строчек?
экономия места и нервов окружающих


Если это четко иерархическая древовидная таблица там СТЕ джоинится по четкой связки parent_id=current_id. Но тут в моем случае для одной записи данные из СТЕ должны браться из двух разных строк. с этим пока проблема.

А таблицу что нужно позже выложу если интересно.
7 июл 15, 14:12    [17862564]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
o-o
Guest
на посторонней таблице без вникания в ваши-приходы-расходы можно увидеть, в чем суть?
не надо вашу таблицу, упрощенно надо показать, что имеете на входе, что хотите на выходе
7 июл 15, 14:21    [17862640]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
o-o,

Сейчас сделал таблицу примерную. ячейки с голубым фоном - исходные данные, с желтым - результаты "многобукф" запроса . Чуть позже опишу как все это должно происходить.
Спасибо.
7 июл 15, 15:24    [17863007]     Ответить | Цитировать Сообщить модератору
 Re: JOIN в CTE  [new]
max aka max
Member

Откуда:
Сообщений: 204
o-o,

К сообщению приложен файл. Размер - 113Kb
7 июл 15, 15:24    [17863009]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить