[一个简单的问题]在最后一行加总

[RobinBest]

表A
单据号 日期 入库量 出库量 结存量
A001 2008-11-1 50 0 50
B002 2008-11-2 0 30 20
B003 2008-11-3 40 50 10
A003 2008-11-4 30 0 40

表B
单据号 日期 入库量 出库量 结存量
A001 2008-11-1 50 0 50
B002 2008-11-2 0 30 20
B003 2008-11-3 40 50 10
A003 2008-11-4 30 0 40
Total -- 120 80 40

[qizhengsheng]

[Quote=引用 3 楼 szx1999 的回复:]
SQL code
if object_id('[表a]') is not null drop table [表a]
go
create table [表a]([单据号] varchar(4),[日期] varchar(10),[入库量] int,[出库量] int,[结存量] int)
insert [表a]
select 'a001','2008-11-1',50,0,50 union all
select 'b002','2008-11-2',0,30,20 union all
select 'b003','2008-11-3',40,50,10 union all
select 'a003','2008-11-4',30,0,40

select * from [表a]
union all
select 'Total','---',sum([…
[/Quote]

顶

[子陌红尘]

group by ... with rollup having ....

[子陌红尘]

group by ... with rollup having ....

[等不到来世]

if object_id('[表a]') is not null drop table [表a]
go
create table [表a]([单据号] varchar(4),[日期] varchar(10),[入库量] int,[出库量] int,[结存量] int)
insert [表a]
select 'a001','2008-11-1',50,0,50 union all
select 'b002','2008-11-2',0,30,20 union all
select 'b003','2008-11-3',40,50,10 union all
select 'a003','2008-11-4',30,0,40

select * from [表a]
union all
select 'Total','---',sum([入库量]),sum([出库量]),sum([入库量])-sum([出库量]) from [表a]

--测试结果:
/*
单据号   日期         入库量         出库量         结存量
----- ---------- ----------- ----------- -----------
a001  2008-11-1  50          0           50
a003  2008-11-4  30          0           40
b002  2008-11-2  0           30          20
b003  2008-11-3  40          50          10
Total ---        120         80          40

(5 row(s) affected)

*/

[水族杰纶]

declare @t table(单据号 varchar(5), 日期 datetime,         入库量 int , 出库量 int, 结存量 int) 
insert @t select 'A001' , '2008-11-1', 50,  0,  50 
insert @t select 'B002' , '2008-11-2' ,0  ,30 , 20 
insert @t select 'B003' , '2008-11-3' ,40 , 50,  10 
insert @t select 'A003',  '2008-11-4' ,30,  0  ,40 
select * from @t
union all
select 'Total',null,sum(入库量),sum(出库量),sum(入库量)-sum(出库量)from @t
/*单据号   日期                                                     入库量         出库量         结存量         
----- ------------------------------------------------------ ----------- ----------- ----------- 
A001  2008-11-01 00:00:00.000                                50          0           50
B002  2008-11-02 00:00:00.000                                0           30          20
B003  2008-11-03 00:00:00.000                                40          50          10
A003  2008-11-04 00:00:00.000                                30          0           40
Total NULL                                                   120         80          40
*/