填桶SQL查询CTE

Question

我想从给定的输入表中获得以下输出:

输入表(待填充的桶)

ID | FullCapacity | CurrentAmount    
---+--------------+--------------
B1 |     100      |     0    
B2 |      50      |     0
B3 |      70      |     0

输入表(填充表)

        ID | Filler            
        ---+-------
        F1 | 90              
        F2 | 70          
        F3 | 40    
        F4 | 20 

输出表应如下所示填充过程。

ID | FullCapacity | CurrentAmount       
---+--------------+--------------
B1 |    100       |    90        
B2 |     50       |     0    
B3 |     70       |     0
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50
B3 |     70       |    10
---+--------------+--------------
B1 |    100       |   100      
B2 |     50       |    50    
B3 |     70       |    50
---+--------------+--------------
B1 |    100       |   100        
B2 |     50       |    50    
B3 |     70       |    70

我正试图从填充物到桶逐个填充这个。我们可以不使用光标做到这一点吗?

请注意，我们可以有多种类型的桶，例如红桶，蓝桶和红色填料，蓝色填料。红色的填料去红色的桶，蓝色的填料去蓝色，以此类推。

谢谢

Answer 1

您可以在SQL Server 2008中这样做:

declare @Buckets table (ID char(2), FullCapacity int)
declare @Filler table (ID char(2), Filler int)
insert into @Buckets 
select 'B1', 100 union all
select 'B2', 50 union all
select 'B3', 70 
insert into @Filler 
select 'F1', 90 union all
select 'F2', 70 union all
select 'F3', 40 union all
select 'F4', 20

select 
    b.ID, 
    b.FullCapacity,
    case 
        when f.TotalFill < b.RunningTotalCapacity then 0
        when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
        else f.TotalFill - b.RunningTotalCapacity
    end as CurrentAmount
from
(
    select      
    ID,
    Filler,
    (
        select sum(f2.Filler)
        from @Filler as f2
        where f2.ID <= f.ID
    ) as TotalFill
    from @Filler as f
) as f
cross join 
(
    select 
        ID,
        FullCapacity, 
        (
            select isnull(sum(b2.FullCapacity), 0)
            from @Buckets as b2
            where b2.ID < b.ID
        ) as RunningTotalCapacity
    from @Buckets as b
) as b
order by f.ID, b.ID

---

你可以使用如下的窗口函数:

SQL Server 2012+

    declare @Buckets table (ID char(2), FullCapacity int)
    declare @Filler table (ID char(2), Filler int)
    insert into @Buckets values
    ('B1', 100),
    ('B2', 50),
    ('B3', 70)
    insert into @Filler values
    ('F1', 90),
    ('F2', 70),
    ('F3', 40),
    ('F4', 20)
    ;with fillerCte as
    (
        select      
            ID,
            Filler,
            sum(Filler) over (order by ID) as TotalFill
        from @Filler
    ), 
    BucketCte as
    (
        select 
            ID,
            FullCapacity,
            sum(FullCapacity) over (order by ID) - FullCapacity as RunningTotalCapacity
        from @Buckets
    )
    select 
        b.ID, 
        b.FullCapacity,
        case 
            when f.TotalFill < b.RunningTotalCapacity then 0
            when f.TotalFill > b.RunningTotalCapacity + b.FullCapacity then b.FullCapacity
            else f.TotalFill - b.RunningTotalCapacity
        end as CurrentAmount
    from fillerCte as f
    cross join BucketCte as b
    order by f.ID, b.ID

Answer 2

所需要做的就是累积和和一些连接。因此，您可以在没有游标的情况下完成此操作。其思想是使用累积连接，然后根据范围将每个填充记录分配给一个或多个桶。

使用ANSI标准语法进行累加和:

select b.*, f.id,
       (greatest(b.cumecap - b.fullcapacity, f.cumefiller - f.filler) - 
        least(b.cumecap, f.cumefiller)
       ) as amount_in_bucket
from (select b.*,
             sum(b.fullcapacity) over (order by id) as cumecap
      from buckets
     ) b join
     (select f.*,
             sum(f.filler) over (order by id) as cumefiller
      from filler f
     ) f
     on f.cumefiller - f.filler <= b.cumecap and
        f.cumefiller >= b.cumecap - b.fullcapacity;

这将生成每个桶的映射以及桶中每个填充物的数量。

注:它利用了函数greatest()和least()。如果功能不可用，这些很容易被case表达式代替。