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

[flyfly2008]

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   ys
1	a	100	130     30
2	a	20	130     10
4	b	20	130     110
5	a	20	130     -10

就是把cp相同的，按照ID大小顺序逐次相减

[liangCK]

这个totalqty悲催得。。。

[liangCK]

select a.id,a.cp,a.qty,a.totalqty,max(a.totalqty)-sum(b.qty)
from #aa as a
    left join #aa as b
on a.cp=B.cp and a.id>=B.id
GROUP BY a.id,a.cp,a.qty,a.totalqty

[开着拖拉机泡妞]

--> 测试数据：[tbl]
go
if object_id('[tbl]') is not null 
drop table [tbl]
go
create table [tbl](
[name] varchar(1),
[date] varchar(5),
[num] int
)
go
insert [tbl]
select 'a','1-1号',1 union all
select 'b','1-2号',4 union all
select 'a','1-3号',8 union all
select 'a','1-4号',5 union all
select 'b','1-5号',6 union all
select 'b','1-6号',9

;with t
as(
select ROW_NUMBER()over(partition by name
order by [date]) as id,
*,num  as total from tbl
),
m as(
select id,name,[date],num,total from t where id=1
union all
select a.id,a.name,a.[date],a.num,b.total+a.num from t a
inner 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	total
a	1-3号	8	8
a	1-4号	5	13
a	1-1号	1	14
b	1-2号	4	4
b	1-5号	6	10
b	1-6号	9	19
*/

[开着拖拉机泡妞]

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 ys
from #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.totalqty
order by cp

/*
id	cp	qty	totalqty	ys
1	a	100	130	30
2	a	20	130	10
5	a	20	130	-10
4	b	20	130	110
*/

[chkaka]

select a.id,a.cp,a.qty,a.totalqty,a.totalqty-(select sum(b.qty) from #aa b where b.id<=a.id and b.cp=a.cp group by b.cp) from #aa as a