雇员表,字段分别为部门名称,小组名称,工资和奖金.
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博客

报表添加合计

最新文章