SQLserver怎样把汇总数显示在结尾

Question

把求个数的汇总结果显示在尾行应该用什么语句 命令应该怎么写啊

Answer 1

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

 */

参考一下