excel分类汇总-Excel零基础学SQL21：union与union all实现分类汇总和总计

今天来完成一个相对复杂的练习。

将左边的表格用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]