报表添加合计

雇员表,字段分别为部门名称,小组名称,工资和奖金.
create table employees
(
    department varchar(20),
    team varchar(20),
    salary int,
    reward int
);
insert into employees values('技术部','a',10,2);
insert into employees values('技术部','a',20,3);
insert into employees values('技术部','c',40,1);
insert into employees values('产品部','d',10,5);
insert into employees values('产品部','d',50,5);
insert into employees values('产品部','e',30,10);
insert into employees values('人力','f',60,2);
insert into employees values('人力','g',30,2);
insert into employees values('人力','g',10,2);


现在要统计每个部门,每个小组的工资和奖金总和


在最后添加合计有4种方式
1.Union All(适合所有数据库,但是效率很低)
select * from 
(
    select department,team,sum(salary),sum(reward) 
    from employees 
    group by department,team order by department,team
) a
union all
select '合计','',sum(salary),sum(reward) from employees ;

因为报表查询,通常不快,使用这种方式,效率更低
通常会造成难以忍受的查询时间.

2.rollup配合GROUPING函数过滤(仅限Oracle)
select * from 
(
    select department,team,sum(salary),sum(reward),
    GROUPING(department) gdepartment ,GROUPING(team) gteam
    from employees 
    group by rollup(department,team)
) where 
(gdepartment=1 and gteam=1)
or 
(gdepartment=0 and gteam=0);


3.GROUPING SETS(仅限Oracle数据库)
select department,team,sum(salary),sum(reward)
from employees 
group by GROUPING SETS((department,team),null)
order by department,team;

4.两次group by(仅限MySQL)
select department,team,sum(s),sum(r) from 
(
    select department,team,sum(salary) s,sum(reward) r 
    from employees 
    group by department,team 
) a group by concat(department,team) with rollup 

或者简化一下
select department,team,sum(salary) s,sum(reward) r 
from employees 
group by concat(department,team ) with rollup



5.自定义变量(仅限MySQL)
select department,team,s,r from 
(
    select department,team,s,r,@a:=@a+s,@b:=@b+r from 
    (
        select department,team,sum(salary) s,sum(reward) r
        from employees
        group by department,team order by department,team
    ) a,(select @a:=0,@b:=0) t1
) b
union all
select '合计','',@a,@b;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1482097/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1482097/