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

Откуда: из-за границы
Сообщений: 2113
declare @minSum int=120;
declare @t table(id int identity , pr int);
insert into @t(pr) 
select 60 UNION ALL
select 50 UNION ALL
select 40 UNION ALL
select 30 UNION ALL
select 20  UNION ALL
select 10;


все суммы упорядочены
для данного примера нужно вытащить только первые 3 значения
так как их сумма больше или равна @minSum и кол-во минимально элементов
как организовать такую выборку ?

то есть нужны номера строк
17 июн 13, 14:44    [14442403]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Glory
Member

Откуда:
Сообщений: 104760
michael R
то есть нужны номера строк

Ну так пронумеровать по убыванию
ROW_NUMBER() OVER
17 июн 13, 14:45    [14442410]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
SELECT id , pr FROM @T P
WHERE 
(SELECT SUM(PR) FROM @T) >=@minSum;


так получаю все строки
а мне нужно только 3
17 июн 13, 14:49    [14442435]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
select MIN(v.number)
from master..spt_values v
cross apply (select SUM(t.pr) spr from @t t where t.id<=v.number ) c
where v.type='P' and spr>=@minSum
17 июн 13, 14:53    [14442472]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
michael R
SELECT id , pr FROM @T P
WHERE 
(SELECT SUM(PR) FROM @T) >=@minSum;


так получаю все строки
а мне нужно только 3

Как написали выше, ну если не кол-во нужно, а вывести именно строки, то:

declare @t table(id int identity , pr int);
insert into @t(pr) 
select 60 UNION ALL
select 50 UNION ALL
select 40 UNION ALL
select 30 UNION ALL
select 20  UNION ALL
select 10;

WITH cte AS(
SELECT ROW_NUMBER() OVER (ORDER BY PR DESC) rn,id,pr FROM @t )

SELECT * FROM cte WHERE rn<=(select MIN(v.number)
from master..spt_values v
cross apply (select SUM(t.pr) spr from @t t where t.id<=v.number ) c
where v.type='P' and spr>=@minSum
)
17 июн 13, 15:00    [14442525]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
with recurs as (
   select t.*, t.pr as spr from @t t where t.id=1
   union all
   select t.*, r.spr+t.pr
      from recurs r
      join @t t on t.id=r.id+1 and r.spr<@minSum
   )
select * from recurs
17 июн 13, 15:14    [14442639]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
а если "ID" не идут подряд 1,2,3
а например с дырками ?
17 июн 13, 15:25    [14442724]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
michael R
а если "ID" не идут подряд 1,2,3
а например с дырками ?

14442525
тут по суммам номера строк... Пофигу на ID)
17 июн 13, 15:26    [14442740]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
автор
тут по суммам номера строк... Пофигу на ID)


declare @minSum int=100;
declare @t table(id int , pr int);
insert into @t(id,pr) 
select 2,60 UNION ALL
select 4,50 UNION ALL
select 5,40 UNION ALL
select 8,30 UNION ALL
select 10,20  UNION ALL
select 15,10;

WITH cte AS(
SELECT ROW_NUMBER() OVER (ORDER BY PR DESC) rn,id,pr FROM @t )

SELECT * FROM cte WHERE rn<=(select MIN(v.number)
from master..spt_values v
cross apply (select SUM(t.pr) spr from @t t where t.id<=v.number ) c
where v.type='P' and spr>=@minSum
);


нее не работает
17 июн 13, 15:29    [14442758]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
michael R
нее не работает

declare @minSum int
SET @minSum=100;
declare @t table(id int , pr int);
insert into @t(id,pr) 
select 2,60 UNION ALL
select 4,50 UNION ALL
select 5,40 UNION ALL
select 8,30 UNION ALL
select 10,20  UNION ALL
select 15,10;

WITH cte AS(
SELECT ROW_NUMBER() OVER (ORDER BY PR DESC) rn,id,pr FROM @t )

SELECT * FROM cte WHERE rn<=(select MIN(v.number)
from master..spt_values v
cross apply (select SUM(t.pr) spr from cte t where t.rn<=v.number ) c
where v.type='P' and spr>=@minSum
);
17 июн 13, 15:31    [14442778]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
michael R
а если "ID" не идут подряд 1,2,3
а например с дырками ?
with 
renum as (
   select 
      row_number()over(order by pr desc)rn,
      pr 
   from @t),
recurs as (
   select t.*, t.pr as spr 
      from renum t 
      where t.rn=1
   union all
   select t.*, r.spr+t.pr
      from recurs r
      join renum t on t.rn=r.rn+1 
      where r.spr<@minSum
   )
select * from recurs
17 июн 13, 16:05    [14443093]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
или
declare @minSum int=120;
declare @t table(id int, pr int);
insert into @t(id,pr) 
select 2,60 UNION ALL
select 4,50 UNION ALL
select 6,40 UNION ALL
select 7,30 UNION ALL
select 8,20  UNION ALL
select 9,10;

select * 
from @t 
where id<=(
   select MIN(v.number)
   from master..spt_values v
   cross apply (select SUM(t.pr) spr from @t t where t.id<=v.number ) c
   where v.type='P' and spr>=@minSum
   )
17 июн 13, 16:10    [14443150]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
michael R
Member

Откуда: из-за границы
Сообщений: 2113
вроде как работает
спасибо всем
17 июн 13, 16:32    [14443347]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Добрый Э - Эх
Guest
michael R,

Если версия сервера позволяет, то всё банально, до неприличия:

--
-- Тестовый набор данных:
with
  t (id, pr) as
    (
      select 1, 60 UNION ALL
      select 2, 50 UNION ALL
      select 3, 40 UNION ALL
      select 4, 30 UNION ALL
      select 5, 20 UNION ALL
      select 6, 10
    )
--
-- Основной запрос (наличие MS SQL Server 2012 обязательно):
select id, pr
  from (
         select id, pr,
                sum(pr) over(order by pr desc) - pr as slide_sum_pr
           from t
       ) as v
 where slide_sum_pr <= 120;

он-лайн проверка на sqlfiddle.com
18 июн 13, 11:03    [14446420]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
aleks2
Guest
Добрый Э - Эх
Если версия сервера позволяет, то всё банально, до неприличия:

И нафига тут костыли из 2012-го?
with
  t (id, pr) as
    (
      select 1, 60 UNION ALL
      select 2, 50 UNION ALL
      select 3, 40 UNION ALL
      select 4, 30 UNION ALL
      select 5, 20 UNION ALL
      select 6, 10
)
select t1.id, t1.pr, sum(t2.pr)
from t t1 inner join t t2 on t1.pr<=t2.pr
group by t1.id, t1.pr
having sum(t2.pr)-t1.pr <= 120
18 июн 13, 13:38    [14447672]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
Добрый Э - Эх
Guest
aleks2,

машина у тебя, случаем, не горбатый запорожец? не надоело нафталиновые решения использовать? ;)
18 июн 13, 13:41    [14447706]     Ответить | Цитировать Сообщить модератору
 Re: Минимальное кол-во элементов для определённой суммы  [new]
aleks2
Guest
Добрый Э - Эх
aleks2,

машина у тебя, случаем, не горбатый запорожец? не надоело нафталиновые решения использовать? ;)


Ээээ, любитель нанотехнологий, зачем оне нужны? Так, для жопоруких инвалидов мозга.
18 июн 13, 19:00    [14450070]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить