sql怎么在最后面添加总计

--创建表if object_id('tempdb..#a','U') is not null

drop table #a

go

create table #a

(

A tinyint,

B char(4),

C smallint,

D tinyint

)

--插入数据

insert into #a (A,B,C,D)

values (1,'张三',100,10),

(2,'李四',200,20),

(3,'王武',300,30),

(4,'李逵',400,40)

--语句

with a as(

select cast(case grouping(A) when 0 then A else cast('合计' as sql_variant) end as char(4)) as A,cast(case grouping(A) when 0 then B else cast('合计' as sql_variant) end as char(4)) as B,sum(C) as C,sum(D) as D,row_number() over(partition by A order by A ) as rn

from #a

group by rollup(A,B)

)

select * from a where rn <>2 order by A

--结果展示

/*

A B C D rn

---- ---- ----------- ----------- --------------------

1 张三 100 10 1

2 李四 200 20 1

3 王武 300 30 1

4 李逵 400 40 1

合计 合计 1000 100 1

(5 行受影响)

*/

用 sum( A库存) over(paritition by a代码 ) 得到A的合计

用 sum( B库存) over(paritition by b代码 ) 得到B的合计

同时这两个可以相加的。你试试

---