解决方法如下:
-创建表
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
本回答被网友采纳