你这里要根据两列做group by,用一句出你的效果我做不到,COALESCE只是一个null值得替换函数,不是聚合函数,它只是可以将with rollup或者with cube的null值替换成‘合计’而已,但是用一句group by 部门 ,员工 with rollup出来的效果跟你要求的会不一样
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
一个SQL做这个工作,我不会,你看这样行不行
set rs0=DEPT表
for i=1 to rs0数
set rs=emp表..sql=select ename,sum(salary) as sal from emp where dno='rs0("dno)' group by ename
.....
set rs=emp表..sql=select sum(salary) as sal from emp where dno='rs0("dno)'
.....
next