代码之家 › 专栏 › 技术社区 › Wei Lin

总值并按某列倒序求和

sql-server sql

Wei Lin · 技术社区 · 7 年前

表ddl:

|id |gooditem|amt  |
|---|--------|-----|
|1  |apple   |1000 |
|2  |apple   |2000 |
|3  |apple   |-3000|
|4  |apple   |500  |
|5  |apple   |1500 |
|6  |pen     |1000 |
|7  |pen     |2000 |
|8  |pen     |-3000|
|9  |pen     |500  |
|10 |pen     |2500 |

CREATE TABLE T
    ([id] int, [gooditem] varchar(5), [amt] int)
;

INSERT INTO T
    ([id], [gooditem], [amt])
VALUES
    (1, 'apple', 1000),
    (2, 'apple', 2000),
    (3, 'apple', -3000),
    (4, 'apple', 500),
    (5, 'apple', 1500),
    (6, 'pen', 1000),
    (7, 'pen', 2000),
    (8, 'pen', -3000),
    (9, 'pen', 500),
    (10, 'pen', 2500)
;

Test Demo Link

逻辑

产品foo总量为2000支,pen总量为3000支
按ID倒序合计金额

预期结果

|id |gooditem|amt  |formula                      |total_amt|
|---|--------|-----|-----------------------------|---------|
|1  |foo     |1000 |2000-1500-500--3000-2000-1000|0        |
|2  |foo     |2000 |2000-1500-500--3000-2000     |1000     |
|3  |foo     |-3000|2000-1500-500--3000          |3000     |
|4  |foo     |500  |2000-1500-500                |0        |
|5  |foo     |1500 |2000-1500                    |500      |
|6  |pen     |1000 |3000-1500-500--3000-2000-1000|0        |
|7  |pen     |2000 |3000-1500-500--3000-2000     |1000     |
|8  |pen     |-3000|3000-1500-500--3000          |2000     |
|9  |pen     |500  |3000-1500-500                |1000        |
|10 |pen     |1500 |3000-1500                    |1500      |

我的尝试和问题

我试着用 rownumber() 然后订购id并使用id和数据。但我对按商品分组有问题 to get total value and sum the value in reverse order by somecolumn

版本

Microsoft SQL Server 2008 R2(RTM)-10.50.1600.1(x64)

我对SQL Server 2008的回答

with cte as (
    select row_number() over (partition by [gooditem] order by id desc) rnk
       ,sum(T1.amt) over (partition by T1.gooditem)  total_amt,* 
    from T T1
)
,cte2 as (
    select T1.gooditem ,T1.id,T1.rnk,T1.amt ,T1.total_amt -T1.amt as total_amt  from cte T1
    where T1.rnk = 1
    union all
    select T1.gooditem ,T1.id,T1.rnk,T1.amt ,T2.total_amt - T1.amt as total_amt from cte T1
    inner join cte2 T2 on T1.[gooditem] = T2.[gooditem] and T1.rnk = T2.rnk+1
)
select T1.id,T1.gooditem,T1.amt ,T1.total_amt
from cte2 T1
order by id

db<>fiddle

1 回复 | 直到 7 年前

Gordon Linoff 7 年前

您正在寻找一个累计金额:

select id, gooditem, amt,
       (2000 - sum(amt) over (partition by gooditem order by id desc)) as total_amt
from t
order by id;

Here 是DB<gt;小提琴。

编辑:

如果需要总数,也可以使用窗口函数计算:

select id, gooditem, amt,
       (sum(amt) over (partition by gooditem) -
        sum(amt) over (partition by gooditem order by id desc)
       ) as total_amt
from t
order by id;

这个 db<>fiddle 非常相似。