桶填充SQL查询CTE [英] Bucket Filling SQL query CTE

问题描述

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

I want to achieve the below output from the given input tables.

输入表（要填充的桶）

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

输入表（填充器表）

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

输出表的下方应显示填充过程。

Output table should have below showing filling process.

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

我正在尝试从填充器到存储桶一一填充。

I am trying to fill this one by one from filler to bucket. Can we do this without using cursor?

请注意我们可以有多种类型的存储桶，例如红色存储桶，蓝色存储桶和红色填充器，蓝色填充器。红色填充物可移至红色桶，蓝色填充物可移至蓝色，依此类推。

Please see that we can have multiple types of buckets for example red bucket, blue bucket and red filler, blue filler. Red filler to go to red bucket, blue filler to blue and so on.

谢谢

推荐答案

您可以在SQL Server 2008中做到这一点像这样：

You can do this in SQL Server 2008 like this:

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

---

您可以使用如下窗口功能来做到这一点：

---

You can do this using windowing functions like this:

    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

这篇关于桶填充SQL查询CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！