select a.id,a.orderdate,a.orderno,a.wareno,a.warename,a.unit,a.inquantity,a.outquantity,a.price,
sum(case when a.flag=1 then b.inquantity else -b.outquantity end) as libquantity,
a.flag,a.ordertype
from jxc a
left join jxc b on a.wareno=b.wareno and a.id>=b.id
group by a.id,a.orderdate,a.orderno,a.wareno,a.warename,a.unit,a.inquantity,a.outquantity,a.price,
a.flag,a.ordertype
sum(case when a.flag=1 then b.inquantity else -b.outquantity end) as libquantity,
a.flag,a.ordertype
from jxc a
left join jxc b on a.wareno=b.wareno and a.id>=b.id
group by a.id,a.orderdate,a.orderno,a.wareno,a.warename,a.unit,a.inquantity,a.outquantity,a.price,
a.flag,a.ordertype
declare @Jxc table([Id] int,[OrderDate] Datetime,[OrderNo] nvarchar(9),[WareNo] nvarchar(3),[WareName] nvarchar(3),[Unit] nvarchar(1),[InQuantity] int,[OutQuantity] int,[Price] int,[Flag] nvarchar(1),[OrderType] nvarchar(4))
Insert @Jxc
select 1,'2008-8-1',N'PT0801-01',N'F01',N'内存条',N'条',N'2',N'0',130,N'1',N'采购入库' union all
select 2,'2008-8-2',N'PT0801-02',N'F01',N'内存条',N'条',N'6',N'0',130,N'1',N'采购入库' union all
select 3,'2008-8-8',N'PR0808-01',N'F01',N'内存条',N'条',N'0',N'3',130,N'0',N'采购退货'Select [Id], [OrderDate],[OrderNo],[WareNo],[WareName],[Unit],[InQuantity],[OutQuantity],[Price],
(select sum(case when [Flag]=0 then -[OutQuantity] else [InQuantity] end) from @Jxc where [Id] <=t.[Id]) as LibQuantity ,
[Flag],[OrderType] from @Jxc t
/*
Id OrderDate OrderNo WareNo WareName Unit InQuantity OutQuantity Price LibQuantity Flag OrderType
----------- ----------------------- --------- ------ -------- ---- ----------- ----------- ----------- ----------- ---- ---------
1 2008-08-01 00:00:00.000 PT0801-01 F01 内存条 条 2 0 130 2 1 采购入库
2 2008-08-02 00:00:00.000 PT0801-02 F01 内存条 条 6 0 130 8 1 采购入库
3 2008-08-08 00:00:00.000 PR0808-01 F01 内存条 条 0 3 130 5 0 采购退货
*/