如何在 access 中使用 sql 实现透视表功能

jiancaisx 2021-08-13

展开全文

transform 语句的应用详解：

1、功能：

用于创建交叉表查询，该查询对于记录计算总计、平均值、计数或其他类型的查询，然后按照两类信息对结果进行分组，一组数据分布在数据表的左侧，另一组数据分布在数据表的顶端。通俗点说，就是使用 sql 实现透视表功能

2、语法：

TRANSFROM aggfunction
SELECT statement
PIVOT pivotfield [ in (vlaue1 [,value2 [, …]] ) ]
对参数的解析：
1、aggfunction： 对所选数据进行操作的 sql 聚合函数，常见的有 count、sum、avg 等函数
2、SELECT statement： 为需要进行的 select 语句
3、pivotfield ： 用来在查询的结果集中创建列标题的字段或表达式
4、value 值： 用于创建列标题的固定值

3、transform 语句与透视表的对应关系：

1）aggfunction： 聚合函数的参数，相当于数据透视表数据区域的值字段，使用聚合函数即设置值字段的汇总方式
2）select statement： 相当于数据透视表的行字段，因为行字段都是不重复的值，所以要使用 group by 去重
3）pivotfield： 相当于数据透视表的列字段，后面的
in (vlaue1 [,value2 [, …]] ) 相当于列字段中项的排序及筛选

下图是对应关系的图解
在这里插入图片描述
实例：
实例1：统计 “测试表” 中，各班级的男女人数

TRANSFORM Sum(测试表.人数) AS 人数合计
SELECT 测试表.班级
FROM 测试表
GROUP BY 测试表.班级
PIVOT 测试表.性别;

《实例1》的返回结果：
在这里插入图片描述

实例2：统计 “测试表” 中，各班级的男生数量

TRANSFORM Sum(测试表.人数) AS 人数之合计
SELECT 测试表.班级
FROM 测试表
GROUP BY 测试表.班级
PIVOT 测试表.性别 in("男");

《实例2》的返回结果：
在这里插入图片描述

4、具体应用：实现行、列的合计，小计

在这里插入图片描述
1）行的合计

/*
对 transform 语句的另一种理解：可以看作是将 select 出来的记录，按 pivotfield 字段分类，对 TRANSFORM 后面的字段进行聚合运算 
*/
TRANSFORM Sum(得分) AS 人数之合计
   SELECT 姓名, 班级, sum(得分) as 总分
 FROM 测试表
GROUP BY 姓名, 班级
order by 班级
    PIVOT 科目;

《行合计》的返回结果：
在这里插入图片描述

2）列的合计

transform sum(T.得分)
   select T.姓名, 
     iif(T.班级="9999","", T.班级) as 班级, 
     sum(得分) as 总得分
   from
  (SELECT 姓名, 班级, 科目, 得分 from 测试表
   union all
   select "合计" as 姓名, "9999" as 班级, 科目, sum(得分)
     from 测试表
    group by 科目) T
    group by T.姓名, T.班级
    order by T.班级
    pivot T.科目

《列合计》的返回结果：
在这里插入图片描述

3）小计

TRANSFORM Sum(得分) AS 人数之合计
   select T.姓名, T.班级, sum(得分) as 总得分
     from
 (SELECT * 
FROM 测试表
   union all
  select "小计" as 姓名, 班级, 科目, sum(得分)
    from 测试表 
   GROUP BY 班级, 科目   
   union all   
  select "合计" as 姓名, "" as 班级, 科目, sum(得分)
    from 测试表 
   GROUP BY 科目) T 
    group by T.姓名, T.班级
order by T.班级 
    PIVOT T.科目;

《小计》的返回结果：
在这里插入图片描述
总结： 列的合计和小计，都是先构造出包含“合计”的数据源，然后对新的数据源进行 transform 操作