select 部门=case when 员工 is null then '合计'else 部门 end ,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest group by [部门],员工 with rollup having 部门<>'合计'
select 部门=case when 员工 is null then 'A合计' else 'A' end,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest where 部门='A' group by 员工 with rollup union select 部门=case when 员工 is null then 'B合计' else 'B' end,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest where 部门='B' group by 员工 with rollup
create table tbtest(部門 varchar(10),員工 varchar(10),工資 numeric(10)) Insert into tbtest select 'A','李','1000' union all select 'B','王','2000' union all select 'A','張','3000' union all select 'B','趙','4000'select 部門=case when 員工 is null then '合計' else 部門 end,員工,工資 from (select 部門,員工,工資=sum(工資) from tbtest group by 部門,員工 with rollup)a where 部門 is not null部門 員工 工資 ----------------------- A 李 1000 A 張 3000 合計 NULL 4000 B 王 2000 B 趙 4000 合計 NULL 6000
select 部门=case when 员工 is null then '合计'else 部门 end ,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest group by [部门],员工 with rollup having grouping(部门)=0
--正规的判断select 部门=case when grouping(员工)=1 then '合计'else 部门 end ,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest group by [部门],员工 with rollup having grouping(部门)=0
select 部門=(case when 員工 is null then '合計' else 部門 end ),員工,工資=sum(工資) from tbtest group by 部門,員工 with cube having 部門<>'合計'
create table test0515(id char(10),nameid char(10),tmoney int) insert into test0515 select 'a','a',1000 union all select 'b','a',2000 union all select 'a','b',2000 union all select 'b','b',1000 union all select 'c','a',1000 select id=case when nameid is null then '' else id end ,isnull(nameid,'合计') ,sum(tmoney) from test0515 group by [id],nameid with rollup having grouping(id)=0drop table test0515
create table tbtest(部门 varchar(10),员工 varchar(10),工资 numeric(10)) Insert into tbtest select 'A','李','1000' union all select 'B','王','2000' union all select 'A','张','3000' union all select 'B','赵','4000'select top 6 部门=case when 员工 is null then '合计'else 部门 end ,员工=COALESCE(员工,'') ,工资=sum(工资) from tbtest group by [部门],员工 with rollup drop table tbtest
create table tbtest(部門 varchar(10),員工 varchar(10),工資 numeric(10)) Insert into tbtest select 'A','李','1000' union all select 'B','王','2000' union all select 'A','張','3000' union all select 'B','趙','4000' select 部門=case when 員工 is null then '合計'else 部門 end ,員工=COALESCE(員工,''),工資 from ( select 部門,員工,工資=sum(工資) from tbtest group by [部門],員工 with rollup ) a where 部門<>'' /* 部門 員工 工資 A 李 1000 A 張 3000 合計 4000 B 王 2000 B 趙 4000 合計 6000*/godrop table tbtest
select 部门=case when 员工 is null then '合计'else 部门 end
,员工=COALESCE(员工,'')
,工资=sum(工资)
from tbtest group by [部门],员工 with rollup having 部门<>'合计'
,工资=sum(工资)
from tbtest
where 部门='A'
group by 员工 with rollup
union
select 部门=case when 员工 is null then 'B合计' else 'B' end,员工=COALESCE(员工,'')
,工资=sum(工资)
from tbtest
where 部门='B'
group by 员工 with rollup
Insert into tbtest
select 'A','李','1000'
union all select 'B','王','2000'
union all select 'A','張','3000'
union all select 'B','趙','4000'select 部門=case when 員工 is null then '合計' else 部門 end,員工,工資 from
(select 部門,員工,工資=sum(工資) from tbtest group by 部門,員工 with rollup)a
where 部門 is not null部門 員工 工資
-----------------------
A 李 1000
A 張 3000
合計 NULL 4000
B 王 2000
B 趙 4000
合計 NULL 6000
,员工=COALESCE(员工,'')
,工资=sum(工资)
from tbtest group by [部门],员工 with rollup
having grouping(部门)=0
,员工=COALESCE(员工,'')
,工资=sum(工资)
from tbtest group by [部门],员工 with rollup
having grouping(部门)=0
select 部門=(case when 員工 is null then '合計' else 部門 end ),員工,工資=sum(工資) from tbtest group by 部門,員工 with cube having 部門<>'合計'
insert into test0515 select 'a','a',1000
union all select 'b','a',2000
union all select 'a','b',2000
union all select 'b','b',1000
union all select 'c','a',1000
select id=case
when nameid is null then ''
else id
end
,isnull(nameid,'合计')
,sum(tmoney)
from test0515
group by [id],nameid
with rollup
having grouping(id)=0drop table test0515
Insert into tbtest
select 'A','李','1000'
union all select 'B','王','2000'
union all select 'A','张','3000'
union all select 'B','赵','4000'select top 6 部门=case when 员工 is null then '合计'else 部门 end
,员工=COALESCE(员工,'')
,工资=sum(工资)
from tbtest
group by [部门],员工 with rollup drop table tbtest
Insert into tbtest
select 'A','李','1000'
union all select 'B','王','2000'
union all select 'A','張','3000'
union all select 'B','趙','4000'
select 部門=case when 員工 is null then '合計'else 部門 end
,員工=COALESCE(員工,''),工資 from
(
select 部門,員工,工資=sum(工資)
from tbtest group by [部門],員工 with rollup
) a where 部門<>''
/*
部門 員工 工資
A 李 1000
A 張 3000
合計 4000
B 王 2000
B 趙 4000
合計 6000*/godrop table tbtest