怎么相同CP，把总数相减。

如何相同CP，把总数相减。高手请进。

SQL code

create table #aa (id int,cp varchar(10),qty int,totalqty int)insert into #aa values(1,'a',100,130)insert into #aa values(2,'a',20,130)insert into #aa values(4,'b',20,130)insert into #aa values(5,'a',20,130)---要得到如下结果：id      cp     qty   totalqty   ys1    a    100    130     302    a    20    130     104    b    20    130     1105    a    20    130     -10

SQL code

select a.id,a.cp,a.qty,a.totalqty,max(a.totalqty)-sum(b.qty)from #aa as a    left join #aa as bon a.cp=B.cp and a.id>=B.idGROUP BY a.id,a.cp,a.qty,a.totalqty
[解决办法]
SQL code
--> 测试数据：[tbl]goif object_id('[tbl]') is not null drop table [tbl]gocreate table [tbl]([name] varchar(1),[date] varchar(5),[num] int)goinsert [tbl]select 'a','1-1号',1 union allselect 'b','1-2号',4 union allselect 'a','1-3号',8 union allselect 'a','1-4号',5 union allselect 'b','1-5号',6 union allselect 'b','1-6号',9;with tas(select ROW_NUMBER()over(partition by nameorder by [date]) as id,*,num  as total from tbl),m as(select id,name,[date],num,total from t where id=1union allselect a.id,a.name,a.[date],a.num,b.total+a.num from t ainner join m b on a.id=b.id+1 and a.name=b.name)select name,[date],num,total from m order by name/*name    date    num    totala    1-3号    8    8a    1-4号    5    13a    1-1号    1    14b    1-2号    4    4b    1-5号    6    10b    1-6号    9    19*/
[解决办法]
SQL code
create table #aa (id int,cp varchar(10),qty int,totalqty int)insert into #aa values(1,'a',100,130)insert into #aa values(2,'a',20,130)insert into #aa values(4,'b',20,130)insert into #aa values(5,'a',20,130)select a.id,a.cp,a.qty,a.totalqty,(max(a.totalqty)-sum(b.qty)) as ysfrom #aa a left join #aa b on a.cp=b.cp and a.id>=B.id group by a.id,a.cp,a.qty,a.totalqtyorder by cp/*id    cp    qty    totalqty    ys1    a    100    130    302    a    20    130    105    a    20    130    -104    b    20    130    110*/