用于创建交叉表查询,该查询对于记录 计算总计、平均值、计数或其他类型的查询,然后按照两类信息对结果进行分组,一组数据分布在数据表的左侧,另一组数据分布在数据表的顶端。通俗点说,就是使用 sql 实现透视表功能
TRANSFROM aggfunction
SELECT statement
PIVOT pivotfield [ in (vlaue1 [,value2 [, …]] ) ]
对参数的解析:
1、aggfunction: 对所选数据进行操作的 sql 聚合函数,常见的有 count、sum、avg 等函数
2、SELECT statement: 为需要进行的 select 语句
3、pivotfield : 用来在查询的结果集中创建列标题的字段或表达式
4、value 值: 用于创建列标题的固定值
1)aggfunction: 聚合函数的参数,相当于数据透视表数据区域的值字段,使用聚合函数即设置值字段的汇总方式
2)select statement: 相当于数据透视表的行字段,因为行字段都是不重复的值,所以要使用 group by 去重
3)pivotfield: 相当于数据透视表的列字段,后面的
in (vlaue1 [,value2 [, …]] ) 相当于列字段中项的排序及筛选
下图是对应关系的图解
TRANSFORM Sum(测试表.人数) AS 人数合计
SELECT 测试表.班级
FROM 测试表
GROUP BY 测试表.班级
PIVOT 测试表.性别;
《实例1》的返回结果:
TRANSFORM Sum(测试表.人数) AS 人数之合计
SELECT 测试表.班级
FROM 测试表
GROUP BY 测试表.班级
PIVOT 测试表.性别 in("男");
《实例2》的返回结果:
/*
对 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.科目
《列合计》的返回结果:
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.科目;
《小计》的返回结果:
联系客服