SQL将卷分割成没有桶ID重复的桶

Question

我解决可能简单，但对我来说不可能的任务在MS SQL。我有两张表，一张是公司名称和用过的瓶子的总量。在第二个表中，我有一个具有唯一ID的桶及其瓶容量的列表。我的任务是为每个公司分配正确的桶数(以覆盖所有瓶的体积)，同时不使用相同的桶两次(不重复桶具有相同的ID为2或moře公司)。有人能帮我吗?谢谢你！

Answer 1

考虑到额外的简化，每个瓶子都是相同的，每个桶都有相同的容量(根据你的评论)，这将达到目的:

-- demo schema
create table companies (cname char, bottlesUsed int);
create table buckets (id int, capacity int);
-- demo data
insert companies values ('a', 41), ('b', 2), ('c', 5), ('d', 50);
insert buckets select top 20 row_number() over (order by object_id), 20 from sys.objects;
with 
bucketnums as 
(
select i = row_number() over (order by id),
id
from   buckets
),
bucketRanges as
(
select   cname,
firstBucketNum = 1 + lag(lastBucketNum, 1, 0) over (order by cname),
lastBucketNum               
from     (  -- running total of bucket count required by each customer
select cname,
lastBucketNum = sum(ceiling(bottlesUsed * 1.0 / 20)) 
over (order by cname rows unbounded preceding)
from   companies
)  t
)

select   conmpanyName = br.cname,
allocatedBucketId = bn.id
from     bucketRanges     br
join     bucketnums       bn on bn.i between firstBucketNum and lastBucketNum;

如果瓶子大小或桶容量是可变的，这个问题变得muchmore…"interesting":)