我知道这样可以大概实现功能: 但是,“合计”的那一行怎么出来呢??? 提示是说要使用COALESCE函数 真不知道怎么用COALESCE(表达式1,表达式2表达式N)好象是当取N个表达式中非零的那个,或是第一个!不知道在这里怎么用????select DEPT.DNAME AS '部门',EMP.ENAME AS '员工',SUM(EMP.SALARY) AS '工资' FROM EMP,DEPT WHERE DEPT.DNO=EMP.DNO GROUP BY DEPT.DNAME,EMP.ENAME WITH ROLLUP
SELECT 部门,员工,SUM(工资) FROM db GROUP BY 部门,员工
一个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
用存储过程实现, 用动态SQL语句~~~~~~~
select 部门 ,员工,sum(工资) as 员工工资 from test group by 部门 ,员工 union all select 部门=部门+'合计',' ',sum(工资) from test group by 部门 order by 部门-结果 部门 员工 员工工资 ---- ---------- ----------- A 张 3000 A 李 1000 A合计 4000 B 赵 4000 B 王 2000 B合计 6000(6 row(s) affected)
这是2句SQL了,可以一句实现吗?使用COALESCE函数。
你这里要根据两列做group by,用一句出你的效果我做不到,COALESCE只是一个null值得替换函数,不是聚合函数,它只是可以将with rollup或者with cube的null值替换成‘合计’而已,但是用一句group by 部门 ,员工 with rollup出来的效果跟你要求的会不一样
楼上说得对,可以用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 rollupdrop table test0515
select 部门=case when 员工 is null then '合计'else 部门end ,员工=COALESCE(员工,'') ,工资=sum(工资) from tb group by [部门],员工 with rollup
但是,“合计”的那一行怎么出来呢???
提示是说要使用COALESCE函数
真不知道怎么用COALESCE(表达式1,表达式2表达式N)好象是当取N个表达式中非零的那个,或是第一个!不知道在这里怎么用????select DEPT.DNAME AS '部门',EMP.ENAME AS '员工',SUM(EMP.SALARY) AS '工资'
FROM EMP,DEPT
WHERE DEPT.DNO=EMP.DNO
GROUP BY DEPT.DNAME,EMP.ENAME
WITH ROLLUP
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
union all
select 部门=部门+'合计',' ',sum(工资) from test group by 部门
order by 部门-结果
部门 员工 员工工资
---- ---------- -----------
A 张 3000
A 李 1000
A合计 4000
B 赵 4000
B 王 2000
B合计 6000(6 row(s) affected)
楼上说得对,可以用with rollup
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 rollupdrop table test0515
,员工=COALESCE(员工,'')
,工资=sum(工资)
from tb group by [部门],员工 with rollup