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

Откуда:
Сообщений: 1424
Имеем таблицу вида:
declare @t table(KMS1 int, Trip1 int, KMS2 int, Trip2 int, KMS3 int, Trip3 int)

С данными:
insert into @t
select 20,	1,	40,	1,	32,	1
union
select 69,	2,	0,	0,	0,	0
union
select 69,	2,	26,	2,	0,	0
union
select 0,	0,	 0,	0,	0,	0						

select * from @t

KMS(1,2,3) - это километраж ездки,
Trips(1,2,3) - это кол-во ездок.

Надо вывести колонку, в которой будет указана норма ездок.
Алгоритм расчета нормы ездок:
1) Если километраж всех ездок (т.е. KMS1*T1+KMS2*T2+KMS3*T3) укладывается в отрезок из некоторых величин, например 70 и 110 (>= 70 AND <= 110), то надо выводить сумму всех трех ездок
2) Если километраж >= 110, тогда следует последовательно приплюсовывать километраж ездок, пока он не станет >= 70 и выводить соотв. число ездок
3) Если километраж < 70 тогда следует вывести MAX(1, (70-(KMS1*T1+KMS2*T2+KMS3*T3))/30)
(т.е. либо 1 либо то, что получится по формуле)

Результат для вышеописанных данных:
2
3
2
2

С реализацией 1 и 3 пунктов алгоритма вопросов не возникает. Возникает вопрос с реализации 2 пункта.

Можно ли реализовать 2 пункт без циклов, т.е. одним запросом?
20 окт 09, 14:52    [7812000]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
iljy
Member

Откуда:
Сообщений: 8711
_ч_,

а зачем тут цикл? у вас 3 (три!) переменные
declare @t table(KMS1 int, Trip1 int, KMS2 int, Trip2 int, KMS3 int, Trip3 int)
insert into @t
select 20,	1,	40,	1,	32,	1
union all
select 69,	2,	0,	0,	0,	0
union all
select 69,	2,	26,	2,	0,	0
union all
select 0,	0,	 0,	0,	0,	0						

select
	 case when s3 between 70 and 110 then Trip1 + Trip2 + Trip3
		  when s1 >= 70 then Trip1
		  when s2 >= 70 then Trip1 + Trip2
		  else 
			case when s4 > 1 then s4 else 1 end
	end
from		  
(
	select *, KMS1*Trip1 s1, KMS1*Trip1 + KMS2* Trip2 s2,
		   KMS1*Trip1 + KMS2* Trip2 + KMS3*Trip3 s3,
		   (70-(KMS1*Trip1+KMS2*Trip2+KMS3*Trip3))/30 s4
	from @t
)t
20 окт 09, 15:08    [7812179]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
_ч_
Member

Откуда:
Сообщений: 1424
iljy, в том то и дело, что неверен ваш запрос и надо последовательно складывать километраж*на одну ездку

для строки select 20, 5, 36, 1, 0, 0
ваш вариант выдает 5, что неверно (должно быть 4 20+20+20+20 >= 70, а вот 20+20+20 < 70)
20 окт 09, 16:57    [7813151]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
_ч_
Member

Откуда:
Сообщений: 1424
Кто-нибудь может направить на путь истинный?

Если нужна версия сервера, то:
Microsoft SQL Server 2005 - 9.00.3054.00
20 окт 09, 16:58    [7813156]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
iljy
Member

Откуда:
Сообщений: 8711
_ч_,

а школьную математику вспомнить не хотим?
declare @t table(KMS1 int, Trip1 int, KMS2 int, Trip2 int, KMS3 int, Trip3 int)
insert into @t
select 20,	1,	40,	1,	32,	1
union all
select 69,	2,	0,	0,	0,	0
union all
select 69,	2,	26,	2,	0,	0
union all
select 0,	0,	 0,	0,	0,	0	
union all
select 20, 5, 36, 1, 0, 0 union all
select 35, 5, 36, 1, 0, 0						

select
	 case when s3 between 70 and 110 then Trip1 + Trip2 + Trip3
		  when s1 >= 70 then cast(ceiling(70./KMS1) as int)
		  when s2 >= 70 then Trip1 + cast(ceiling((70.-s1)/KMS2) as int)
		  else 
			case when s4 > 1 then s4 else 1 end
	end
from		  
(
	select *, KMS1*Trip1 s1, KMS1*Trip1 + KMS2* Trip2 s2,
		   KMS1*Trip1 + KMS2* Trip2 + KMS3*Trip3 s3,
		   (70-(KMS1*Trip1+KMS2*Trip2+KMS3*Trip3))/30 s4
	from @t
)t
20 окт 09, 17:16    [7813288]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
_ч_
Member

Откуда:
Сообщений: 1424
iljy, не хотим. не все так просто и на этот раз вы пытаетесь упростить мою задачу

строка:
select 10, 1, 20, 5, 0, 0

выдает результат 6 (!)
Но должна выдать 4.

сумма 10 + 20+20+20 >= 70, до нее 10+20+20 < 70, поэтому запрос по этой строке данных должен выдать число ездок 1+1+1+1 = 4

Сам сообразить до сих пор не могу как одним запросом провести итерацию без цикла
21 окт 09, 08:50    [7815210]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
iljy
Member

Откуда:
Сообщений: 8711
_ч_,

А ничего что сумма 10 + 20+20+20 = 110 и срабатывает первое условие?
_ч_
Если километраж всех ездок (т.е. KMS1*T1+KMS2*T2+KMS3*T3) укладывается в отрезок из некоторых величин, например 70 и 110 (>= 70 AND <= 110))

Если вам надо, чтобы s3 < 110 - четче ставьте задачу. И переделайте первое условие на s3 >=70 and s3 < 110
21 окт 09, 11:22    [7816180]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
_ч_
Member

Откуда:
Сообщений: 1424
iljy, извините, действительно ошибся
21 окт 09, 11:49    [7816455]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
Leierkastenmann
Member

Откуда: Москва
Сообщений: 113
_ч_,

Может быть спасет вот такой вариант?

declare @t table(KMS1 int, Trip1 int, KMS2 int, Trip2 int, KMS3 int, Trip3 int)
insert into @t
select 20,	1,	40,	1,	32,	1
union all
select 69,	2,	0,	0,	0,	0
union all
select 69,	2,	26,	2,	0,	0
union all
select 0,	0,	 0,	0,	0,	0	
union all
select 20, 5, 36, 1, 0, 0 union all
select 10, 1, 20, 5, 0, 0					

select

	case 
		when KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3 >= 70 and KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3 < 110  then Trip1 + Trip2 + Trip3 
		when KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3 < 70 then case
									when (70 -(KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3))/30 < 1 then 1
									else (70 -(KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3))/30
								   end
		else
			case
				when KMS1*Trip1 >= 70 then ceiling(70./KMS1)
				else
					case 
						when KMS1*Trip1 + KMS2*Trip2 >= 70 then Trip1 + ceiling((70. - KMS1*Trip1)/KMS2)
						else
							case 
								when KMS1*Trip1 + KMS2*Trip2 + KMS3*Trip3 >= 70 then Trip1 + Trip2 + ceiling((70. - KMS1*Trip1 - KMS2*Trip2)/KMS2)
								else Trip1 + Trip2 + Trip3
							end
					end
			end
	end

from @t

Я написал кейсы непосредственно к начальной таблице, можно выделить все повторяющиеся параметры, вычислить их в подзапросе и потом уже на него напялить кейс. Но вам же идея была нужна :)
21 окт 09, 12:40    [7816945]     Ответить | Цитировать Сообщить модератору
 Re: итерация без цикла?  [new]
_ч_
Member

Откуда:
Сообщений: 1424
Leierkastenmann, спасибо большое за идею и за помощь
я уже решил свою задачу не без помощи iljy
21 окт 09, 14:59    [7818239]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить