create table #t(a int,b int,c int,d int,e int) insert into #t values(1,2,3,4,5) insert into #t values(1,2,3,4,6) insert into #t values(1,2,3,4,7) insert into #t values(1,2,3,4,8) insert into #t values(1,3,3,4,5) insert into #t values(1,3,3,4,6) insert into #t values(1,3,3,4,8) insert into #t values(1,3,3,4,7)insert into #t values(2,2,2,4,5) insert into #t values(2,2,3,4,6) insert into #t values(2,2,4,4,7) insert into #t values(2,2,5,4,8) insert into #t values(2,3,6,4,5) insert into #t values(2,3,3,4,6) insert into #t values(2,3,3,4,8) insert into #t values(2,3,3,4,7) goSELECT case when grouping(a)=1 then '合计' else cast(a as varchar) end a, sum(b) b,sum(c) c,sum(d) d,sum(e) e from #t group by a with ROLLUPdrop table #t/*****************a b c d e ------------------------------ ----------- ----------- ----------- ----------- 1 20 24 32 52 2 20 29 32 52 合计 40 53 64 104(3 行受影响)
楼主也可以用union(区分重发)或者union all(不区分重复)来做。
除了number列是int外 其他列是varchar
create table tb ( id int identity(1,1) primary key, name varchar(50), number int ) go insert into tb values('华硕',1) insert into tb values('华硕',1) insert into tb values('华硕',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) 最后结果要 合计=13 和select * from tb的结果
create table tb ( id int identity(1,1) primary key, name varchar(50), number int ) go insert into tb values('华硕',1) insert into tb values('华硕',1) insert into tb values('华硕',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('华擎',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1) insert into tb values('技嘉',1)select * from tb union all select null,'合计',sum(number) from tbdrop table tb
select *,null as 合计 from tb union all select null,null,null,ltrim(sum(number)) from tb
select *,null as 合计 from tb union all select null,null,null,sum(number) from tb
select a.*,b.cnt as total from tb a join (select count(1) as cnt from tb) b on 1=1
select *,(select count(1) as cnt from tb) as 合计 from tb
select *,(select sum(number) from tb) as 合计 from tb
create table #t(a int,b int,c int,d int,e int)
insert into #t values(1,2,3,4,5)
insert into #t values(1,2,3,4,6)
insert into #t values(1,2,3,4,7)
insert into #t values(1,2,3,4,8)
insert into #t values(1,3,3,4,5)
insert into #t values(1,3,3,4,6)
insert into #t values(1,3,3,4,8)
insert into #t values(1,3,3,4,7)insert into #t values(2,2,2,4,5)
insert into #t values(2,2,3,4,6)
insert into #t values(2,2,4,4,7)
insert into #t values(2,2,5,4,8)
insert into #t values(2,3,6,4,5)
insert into #t values(2,3,3,4,6)
insert into #t values(2,3,3,4,8)
insert into #t values(2,3,3,4,7)
goSELECT
case
when grouping(a)=1 then '合计'
else cast(a as varchar) end a,
sum(b) b,sum(c) c,sum(d) d,sum(e) e
from #t
group by a
with ROLLUPdrop table #t/*****************a b c d e
------------------------------ ----------- ----------- ----------- -----------
1 20 24 32 52
2 20 29 32 52
合计 40 53 64 104(3 行受影响)
create table tb
(
id int identity(1,1) primary key,
name varchar(50),
number int
)
go
insert into tb values('华硕',1)
insert into tb values('华硕',1)
insert into tb values('华硕',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
最后结果要 合计=13 和select * from tb的结果
(
id int identity(1,1) primary key,
name varchar(50),
number int
)
go
insert into tb values('华硕',1)
insert into tb values('华硕',1)
insert into tb values('华硕',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('华擎',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)
insert into tb values('技嘉',1)select * from tb
union all
select null,'合计',sum(number)
from tbdrop table tb
union all
select null,null,null,ltrim(sum(number))
from tb
union all
select null,null,null,sum(number)
from tb
select a.*,b.cnt as total
from tb a
join (select count(1) as cnt from tb) b on 1=1