累加

[bartholomew4]

想实现添加一个字段显示的值为‘ 应收应付’=（前一行）‘应收应付’（第一行则为0）+（本行）应收应付变化

select a.orderId,
(select isnull(sum(b.Amount),0)) 金额,
(select isnull(sum(b.Amount),0)) 应收应付变化,
(select SUM(b.num)) 总数量,
(select COUNT(b.proId))型号数,

p.pro_name 商品名称,
d.proImg 图片
from myOrders a
inner join orderDetails b on b.orderId=a.orderId
inner join (select orderid,proid from orderDetails where itemNo=1 ) c on c.orderId=b.orderId
inner join (select Pro_ID,Pro_Name from Product) p on p.Pro_ID=c.proId
inner join (select proid,proImg from proImage where itemno=1) d on d.proId=c.proId
where a.loginid=@loginId and (a.orderdate between @startDate and @endDate)
and (a.[status]=4 or a.[status]=5) and price<>0
group by a.orderId,c.proId,p.Pro_Name,d.proImg

[chuanzhang5687]

你最好给出点点测试数据，和你想要的结果。

[bartholomew4]

数据真不好给，因为涉及好几张表。。。。。

这个是想要的结果图（NULL的那个字段就是要实现的部分）

这个是本段程序运行以后的结果

[中国风]

;with C
as
(
select 
	a.orderId,
	isnull(sum(b.Amount),0) 金额,
	SUM(b.num) 总数量,
	COUNT(b.proId)型号数,
	p.pro_name 商品名称,
	d.proImg 图片,
	a.orderdate
from myOrders a 
	inner join orderDetails b on b.orderId=a.orderId 
	inner join (select orderid,proid from orderDetails where itemNo=1 ) c on c.orderId=b.orderId
	inner join (select Pro_ID,Pro_Name from Product) p on p.Pro_ID=c.proId
	inner join (select proid,proImg from proImage where itemno=1) d on d.proId=c.proId
where a.loginid=@loginId and (a.orderdate between @startDate and @endDate) and (a.[status]=4 or a.[status]=5) and price<>0
group by a.orderId,c.proId,p.Pro_Name,d.proImg
)
select 
	*,
	[应收应付变化]=(select sum(金额) from C where proId=a.proId and orderdate<=a.orderdate)
from C as a 

[bartholomew4]

[应收应付变化]=(select sum() from C where proId=a.proId and orderdate<=a.orderdate)
sum里填什么- -

[bartholomew4]

结果还是NULL。。。。。

[AcHerat]

select a.orderId,
(select isnull(sum(b.Amount),0)) 金额,
(select isnull(sum(b.Amount),0)) 应收应付变化,
(select SUM(b.num)) 总数量,
(select COUNT(b.proId))型号数,
p.pro_name	 商品名称,
d.proImg 图片,
rid=identity(int,1,1)
    into #tb
from myOrders a  
inner join orderDetails b on b.orderId=a.orderId  
inner join (select orderid,proid from orderDetails where itemNo=1 ) c on c.orderId=b.orderId
inner join (select Pro_ID,Pro_Name from Product) p on p.Pro_ID=c.proId
inner join (select proid,proImg from proImage where itemno=1) d on d.proId=c.proId
where a.loginid=@loginId and (a.orderdate between @startDate and @endDate)  
and (a.[status]=4 or a.[status]=5) and price<>0
group by a.orderId,c.proId,p.Pro_Name,d.proImg

select *,
     [应收应付] = isnull((select sum(应收应付变化) from #tb where rid <> 1 and rid <= t.rid),0)
from #tb t

[AcHerat]

;with C
as
(
select 
    a.orderId,
    isnull(sum(b.Amount),0) 金额,
    SUM(b.num) 总数量,
    COUNT(b.proId)型号数,
    p.pro_name 商品名称,
    d.proImg 图片,
    a.orderdate,
    rid=row_number() over (order by getdate())
from myOrders a 
    inner join orderDetails b on b.orderId=a.orderId 
    inner join (select orderid,proid from orderDetails where itemNo=1 ) c on c.orderId=b.orderId
    inner join (select Pro_ID,Pro_Name from Product) p on p.Pro_ID=c.proId
    inner join (select proid,proImg from proImage where itemno=1) d on d.proId=c.proId
where a.loginid=@loginId and (a.orderdate between @startDate and @endDate) and (a.[status]=4 or a.[status]=5) and price<>0
group by a.orderId,c.proId,p.Pro_Name,d.proImg
)
select 
    *,
    [应收应付]=isnull((select sum(应收应付变化) from C where rid<>1 and rid <= t.rid),0)
from C t 

[idonot]

累加：
select t1,SUM(t2) as newT1 from test group by t1
http://hi.csdn.net/space.php?uid=856301&do=album&id=97448

[chuanzhang5687]

看楼上两位大虾的吧

[hhwydwfg]

DDD

[hhwydwfg]

DDD

[hhwydwfg]

DDD

[bartholomew4]

感谢两位大虾，功能上是实现了，可是效率很不高，还是决定用C#来实现累加了，分数就平分给回答过的吧