Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
Подскажите как можно распределить столбец2 по столбцу1 при помощи Update

содержимое таблицы

столбец1 столбец2 столбец3( распределение)
100 500 0 (500-100 закрывается полностью)
250 500 0 (400-250 закрывается полностью)
300 500 150 (150 -300=-150 закрывается не полностью )

вообщем надо взять 500 из столбца2 и распределить их на 100,250,300
500 распределяем на 100 остается 400, 400 распределяем на 250 остается 150, 150 распределяем на 300 остается -150
14 дек 11, 14:11    [11764116]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
Задача аналогична задаче "нарастающий итог"?
Смотрите в FAQ.
14 дек 11, 14:26    [11764300]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
С нарастающим итогом вроде понятно
ИД Ресурсов
позиции
select a.[ИД Ресурсов позиции сметы],
max(a.[Количество заявки в единицах ПП]) as sum0,
max(a.КоличествоРесурсаПоПозицииСметы) as sum1,
sum(isnull(b.КоличествоРесурсаПоПозицииСметы, 0))+max(a.КоличествоРесурсаПоПозицииСметы) as tot_sum
from t3 a
left outer join t3 b on b.[ИД Ресурсов позиции сметы] < a.[ИД Ресурсов позиции сметы]
group by a.[ИД Ресурсов позиции сметы

сметы sum0 sum1 tot_sum
1014789 1083.00000000000000 3011.0000000000000 3011.0000000000000
1014817 1083.00000000000000 164.1255000000000 3175.1255000000000
1014886 1083.00000000000000 93.3800000000000 3268.5055000000000
14 дек 11, 15:21    [11764873]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
как сделать чтобы 1083 закрыло 164, затем 93, затем 3011?
пишу вот так не получается
select a.[ИД Ресурсов позиции сметы],
max(a.[Количество заявки в единицах ПП]) as sum0,
max(a.КоличествоРесурсаПоПозицииСметы) as sum1,
sum(isnull(b.КоличествоРесурсаПоПозицииСметы, 0))-max(a.[Количество заявки в единицах ПП]) as tot_sum
from t3 a
left outer join t3 b on b.[ИД Ресурсов позиции сметы] < a.[ИД Ресурсов позиции сметы]
group by a.[ИД Ресурсов позиции сметы]
14 дек 11, 15:23    [11764892]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
DECLARE @sum INT SET @sum = 500

DECLARE @T TABLE (ID INT PRIMARY KEY CLUSTERED IDENTITY, Value INT, Result Int)
INSERT  @t(value)
SELECT 100
UNION
SELECT 250
union
SELECT 300

SELECT ID, Value, @Sum - (ISNULL((SELECT SUM(value) FROM @T B WHERE B.ID< A.ID),0)+value) FROM @T A ORDER BY ID


Можно добавить инструкции case исходя из логики.
14 дек 11, 15:37    [11765046]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
Deff,
подскажите как паралельно выбирать и распределять значение подобные 500, чтобы распределять остальные строки таблицы
14 дек 11, 16:02    [11765331]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
Покажите пример исходных данных в виде
DECLARE @T TABLE (ID INT PRIMARY KEY CLUSTERED IDENTITY, Value INT, Result Int, [ЕЩЕПОЛЯ] int)
INSERT  @t(value, [ЕЩЕПОЛЯ])
SELECT 100, somevalue
UNION
SELECT 250,  somevalue
union
SELECT 300, somevalue
...


И что хотите получить?
14 дек 11, 16:15    [11765449]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

Так что ли?

declare @t table(ID int identity(1,1),col1 int, col2 int)

insert into @t
select 100, 500
union
select 250, 500
union
select 300, 500


select col1, col2, 
case when (col2 - (ISNULL((select SUM(col1) from @t B where B.ID< A.ID),0)+col1)) <0 then (col2 - (ISNULL((select SUM(col1) from @t B where B.ID< A.ID),0)+col1))*-1 else 0 end as col3 
from @T A 
order by ID
14 дек 11, 16:20    [11765527]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
_ч_,
declare @t table(ID int identity(1,1),col0 int, col1 int, col2 int)

insert into @t
select 5,100, 500
union
select 5,250, 500
union
select 5,300, 500

insert into @t
select 7,100, 600
union
select 7,250, 600
union
select 7,300, 600

select * from @T

вот такая таблица
распределять надо в пределах заявки №5, №7
14 дек 11, 16:36    [11765717]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

declare @t table(Id1 int identity(1,1), ID int,col1 int, col2 int)

insert into @t
select 5,100, 500
union
select 5,250, 500
union
select 5,300, 500

insert into @t
select 7,100, 600
union
select 7,250, 600
union
select 7,300, 600


select ID

, col1, col2, 
case when (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))*-1 else 0 end as col3 
from @T A 
order by ID


Если у Вас нет колонки identity (id1 в примере), то можно воспользоваться row_number() например
14 дек 11, 16:59    [11765943]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
declare @t table(Id1 int identity(1,1), ID int,col1 int, col2 int)

insert into @t
select 5,100, 500
union
select 5,250, 500
union
select 5,300, 500

insert into @t
select 7,100, 600
union
select 7,250, 600
union
select 7,300, 600


select ID

, col1, col2, 
case when (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))*-1 else 0 end as col3 
from @T A 
order by ID1
14 дек 11, 17:00    [11765949]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
Не многим сложнее. Главное что бы для col0 всегда был один и тот же col2.
SELECT ID, col0 , col1, col2, 
col2 - (ISNULL((SELECT SUM(col1) FROM @T B WHERE B.ID< A.ID AND B.col0 = A.col0),0)+col1) 
FROM @T A ORDER BY /*Col0,*/ID
14 дек 11, 17:01    [11765961]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
_ч_, спасибо
14 дек 11, 17:04    [11765988]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
Deff,подскажите почему в таблице которую Вы создаете
declare @t table(Id1 int identity(1,1), ID int,col1 int, col2 int)
все работает,
а в моей таблице нет?
CREATE TABLE [dbo].[t3](
Id1 int identity(1,1),
[ID] [int] NOT NULL,
[col2] [decimal](38, 14) NULL,
[col1] [decimal](38, 13) NULL,
)

select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0 end as col3
from t3 A
order by ID
15 дек 11, 09:34    [11768805]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

напишите какие данные и куда вы вставляете. Кроме того вы сам запрос не совсем так, как в моем сообщении написали.
15 дек 11, 09:57    [11768929]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
_ч_,
CREATE TABLE [dbo].[t3](
Id1 int identity(1,1),
[ID] [int] NOT NULL,
[col2] [decimal](38, 14) NULL,
[col1] [decimal](38, 13) NULL,
)

insert into t3
select 69,93.3800000000000, 1083.00000000000000
union
select 69,3011.0000000000000, 1083.00000000000000
union
select 69,164.1255000000000, 1083.0000000000000

select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0 end as col3
from t3 A
order by ID
15 дек 11, 10:34    [11769140]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
Deff
Member

Откуда: Пермь
Сообщений: 18328
"order by ID1"
15 дек 11, 10:36    [11769156]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
Deff, а вот это с тем же запросом работает правильно
------------------------/*****
declare @t table(Id1 int identity(1,1), ID int,col1 int, col2 int)

insert into @t
select 69,93.3800000000000, 1083.00000000000000
union
select 69,3011.0000000000000, 1083.00000000000000
union
select 69,164.1255000000000, 1083.00000000000000
select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0 end as col3
from @t A
order by ID
------------------------/*****
15 дек 11, 10:42    [11769201]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

Ну и в чем проблема? У меня все работает:

CREATE TABLE [dbo].[t3](
Id1 int identity(1,1),
[ID] [int] NOT NULL,
[col2] [decimal](38, 14) NULL,
[col1] [decimal](38, 13) NULL,
)

insert into t3(ID, col1, col2)
select 69,93.3800000000000, 1083.00000000000000
union
select 69,3011.0000000000000, 1083.00000000000000
union
select 69,164.1255000000000, 1083.0000000000000

select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then -1*(col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0 end as col3
from t3 A
order by ID1 


drop table t3
15 дек 11, 10:44    [11769211]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

дело то все в том, что при insert into t3 надо указывать колонки, в которые вы будете вставлять данные.

Почувствуйте разницу:
insert into t3--(ID, col1, col2)
select 69,93.3800000000000, 1083.00000000000000
union
select 69,3011.0000000000000, 1083.00000000000000
union
select 69,164.1255000000000, 1083.0000000000000

select * from t3

truncate table t3 

insert into t3(ID, col1, col2)
select 69,93.3800000000000, 1083.00000000000000
union
select 69,3011.0000000000000, 1083.00000000000000
union
select 69,164.1255000000000, 1083.0000000000000

select * from t3
15 дек 11, 10:47    [11769232]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

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

спасибо
15 дек 11, 10:55    [11769304]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
_ч_, подскажите а как сделать чтобы распределяло и в случае когда суммы в col1 идут по убыванию или вообще не упорядочены, т.е сумм 1083 должна закончиться уже на цифре 3011, а остальные цифры должны остаться без изменения ?

CREATE TABLE [dbo].[t3](
Id1 int identity(1,1),
[ID] [int] NOT NULL,
[col2] [decimal](38, 14) NULL,
[col1] [decimal](38, 13) NULL,
)


truncate table t3
insert into t3(ID, col1, col2)
select 69,3011.0000000000000, 1083.00000000000000
insert into t3(ID, col1, col2)
select 69,164.1255000000000, 1083.0000000000000
insert into t3(ID, col1, col2)
select 69,93.3800000000000, 1083.00000000000000

select * from t3

select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 then -1*(col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0 end as col3
from t3 A
order by ID1
15 дек 11, 12:06    [11769934]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________
_ч_, подскажите а как сделать чтобы распределяло и в случае когда суммы в col1 идут по убыванию или вообще не упорядочены, т.е сумм 1083 должна закончиться уже на цифре 3011, а остальные цифры должны остаться без изменения ?


https://www.sql.ru/forum/actualthread.aspx?tid=127456
пункт 6 для Вас.
Особенно:
"описание желаемого результата на примере тестовых данных"
15 дек 11, 13:06    [11770490]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
МИхаил__________________
Member

Откуда:
Сообщений: 573
_ч_,
таблица упорядочена по Дата, нужно распределить col2 по col1 c учетом упорядочивания по дате, запись с более ранней датой закрывается первой

declare @t table(Id1 int identity(1,1), id int,col1 int,
[Дата] [datetime] NULL,
col2 int)

insert into @t(id,col1,Дата,col2)
select 5,3000,'20110101', 500
insert into @t(id,col1,Дата,col2)
select 5,250,'20110102', 500
insert into @t(id,col1,Дата,col2)
select 5,100,'20110107', 500

select Дата,ID1,ID, col1, col2,
(ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+ col1) as sss,
case when (col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0
then -1*(col2 - (ISNULL((select SUM(col1) from @t B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) else 0
end as col3
from @t A
order by ID1
результатз этого запроса
Дата__________________ID1__ID__col1__col2_col3
2011-01-01 00:00:00.000 1 5 3000 500 2500
2011-01-02 00:00:00.000 2 5 250 500 2750
2011-01-07 00:00:00.000 3 5 100 500 2850


2500 строка 1 правильный результат
2750 строка 2 должно быть 0
2850 строка 3 должно быть 0
15 дек 11, 14:39    [11771391]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите как можно распределить столбец2 по столбцу1 при помощи Update?  [new]
_ч_
Member

Откуда:
Сообщений: 1446
МИхаил__________________,

CREATE TABLE [dbo].[t3](
Id1 int identity(1,1),
[ID] [int] NOT NULL,
[col2] [decimal](38, 14) NULL,
[Дата] [datetime] NULL,
[col1] [decimal](38, 13) NULL,
)


insert into t3(id,col1,[Дата], col2)
select 5,3000,'20110101', 500
insert into t3(id,col1,[Дата], col2)
select 5,250,'20110102', 500
insert into t3(id,col1,[Дата], col2)
select 5,100,'20110107', 500
insert into t3(id,col1,[Дата], col2)
select 7,100, getdate(), 600
insert into t3(id,col1,[Дата], col2)
select 7,250, getdate(), 600
insert into t3(id,col1,[Дата], col2)
select 7,300, getdate(), 600

select ID1,ID, col1, col2,
case when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID=A.ID and B.Id1<A.Id1),0)))<0 then 0 else
case 
when (col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1))<0 
then -1*(col2 - (ISNULL((select SUM(col1) from t3 B where B.ID= A.ID and B.Id1<A.Id1),0)+col1)) 
else 0 end end as col3
from t3 A
order by ID1 
15 дек 11, 14:45    [11771450]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить