今天来完成一个相对复杂的练习。
将左边的表格用SQL语句进行分类汇总，姓名计数，评分求和。
1、生成每个部门的汇总表格。
select 部门,
count(*) as 人数,
sum(评分) as 评分
from
[201$a1:c]
group by 部门
2、将原始的表格和1中生成的表格上下合并，使用union
select 部门,姓名,评分
from [201$a1:c]
union
select 部门 &" 汇总",人数,评分
from
(select 部门,count(*) as 人数,sum(评分) as 评分 from [201$a1:c] group by 部门)
也可以少套一个子查询，将sql语句写成下面这样，保证列数相等即可。
select 部门,
姓名,
评分
from [201$a1:c]
union
select 部门 & " 汇总",
count(*) as 人数,
sum(评分) as 评分
from [201$a1:c] group by 部门
这里使用union上下合并两个表格的数据，是因为union有排序的功能。
3、生成总计表格
select distinct '总计' as 部门总计,
(select count(姓名) from [201$a1:c]) as 人数,
(select sum(评分) from [201$a1:c]) as 总评分
from [201$a1:c]
4、使用union all合并总计表格
select 部门,姓名,评分 from [201$a1:c]
union
select 部门 &" 汇总",人数,评分
from
(select 部门,count(*) as 人数,sum(评分) as 评分
from [201$a1:c]
group by 部门)
union all
select distinct '总计' as 部门总计,
(select count(姓名) from [201$a1:c]) as 人数,
(select sum(评分) from [201$a1:c]) as 总评分
from [201$a1:c]