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 行受影响)

*/

比如：

select ta.订单号, ta.款号, ta.材料代号, ta.材料名称 ,ta.用量,tb.

合计 as 合计 from test_db as ta

left join

(

select 订单号,材料名称,sum(用量) as 合计 from test_db

group by 订单号,材料名称

) as tb

on ta.订单号=tb.订单号 and ta.材料名称=tb.材料名称

已经在sql server 上测试通过。

---