微优网Excel攻略板块,专业的Excel攻略站!

|返回首页|

当前位置:首页office攻略Excel攻略→Excel VBA ADO SQL入门教程17:如何使用SQL制作交叉式报表?

Excel VBA ADO SQL入门教程17:如何使用SQL制作交叉式报表?

时间:2019-11-07人气:71作者:微优网Excel攻略
如何用SQL将一维表转二维表,或者说透视表的的效果,但有点儿烧脑,不是必会的。

 1.

诸君好,我们今天聊SQL查询语句中的交叉表查询。

先说下什么是交叉表。

赌五根黄金,交叉表这个名字你可能陌生,但样子却肯定不陌生,赢了算我的,输了算你的……

简而言之,交叉表就是一种分类汇总的二维表,由行和列两个变量汇总数据,例如下图所示的表格即是一份交叉表,成绩由姓名(行)和学科(列)共同分组定义:

          

在SQL IN EXCEL中,实现交叉表查询的语句是TRANSFORM,其语法如下:

TRANSFORM aggfunction SELECT statement PIVOT pivotfield [IN (value1[, value2[, ...]])]

语法看不懂哦?看不懂才正常呀,一眼就看懂那就扫地僧了不是?

          

 2.

我们在第一章的时候讲过,对于没有VBA编程基础的EXCELer而言,SQL常和透视表搭配使用——透视表相信大家是不陌生的;它有四块区域构成,分别是筛选、行、列和值。

          

好端端的,怎么又扯到透视表去了呢?

手拿开,男男授受不亲,没事乱摸我额头作甚,哥没烧糊涂。

我们之前说TRANSFROM语法如下:

TRANSFORM aggfunction SELECT statement PIVOT pivotfield [IN (value1[, value2[, ...]])]

以透视表来说,TRANSFORM 后的aggfunction,对应的是透视表的值区域SELECT的statement对应的是透视表的行字段(透视表的行字段肯定是去重归类的),而PIVOT则是对应透视表的列字段

因此TRANSFROM的语法汉化后如下:

TRANSFROM 聚合值字段

SELECT 行字段 FROM 数据源 GROUP BY 分组行字段

PIVOT 分组列字段 ……

说好的SELECT指定行字段,怎么又多出来FROM 和 GROUP BY子句了呢?这是因为SELECT是指SELECT语句,而不是SELECT子句

SELECT 行字段 FROM 数据源 GROUP BY 分组行字段

将这句作为一个整体来看,是不是一条我们所熟悉的SELECT查询语句?摊手。

FROM子句指定数据源,如果没有数据源,查询也就成了无水之源。

GROUP BY子句对数据源指定的行字段进行去重分类。前面说过,交叉表是按行列两个方向分类汇总的二维表,倘若没有GROUP BY子句对行字段进行去重分类,之后的汇总也就成了问题。

最后由SELECT子句指定交叉表显示的行字段。 因此,标红色的部分,作为一条完整SELECT语句,指定了数据源以及交叉表的行字段。 ……

照例举个例子。

如下图所示,是一份‘成绩表’。

          

我们希望通过SQL的交叉表查询方法,实现查询结果如下:

          

语句如下:

TRANSFORM SUM(成绩) SELECT 姓名 FROM [成绩表$]  GROUP BY 姓名 PIVOT 学科 标红色的SELECT语句,指定了交叉表的数据源(成绩表)及分类汇总的行字段,也就是姓名。           

PIVOT则指定了交叉表的列字段,也就是学科。           

TRANSFORM SUM(成绩),通过聚合函数SUM,对在行字段和列字段共同分组定义下的值,也就是成绩进行求和运算,最终得到一份如结果所示的交叉表。

 3.

……如果我们想在列字段增加一个字段名,例如‘英语’,该如何处理呢?

比如,如下图酱紫的结果:

         

          

有朋友说,数据源并不存在‘英语‘这门学科呀!

是的,但这并不妨碍我们无中生有没事找事啊。无赖脸。

事实上,在实际工作中也是有此类需要的。比如,汇总展示全年的数据,你不能因为12月份没有数据,就不体现12月份的数据……。

在TRANSFORM语句的关键字PIVOT后面,有一个可选的IN语句:PIVOT pivotfield [IN (value1[, value2[, ...]])],它可以对列字段进行筛选删除或增加。

使用以下语句,我们即可实现在列字段中新增字段名‘英语’。

TRANSFORM SUM(成绩) SELECT 姓名 FROM [成绩表$]  GROUP BY 姓名  PIVOT 学科 IN(语文,数学,英语) 而如果我们需要删除‘数学’,只保留‘语文’和‘英语’两个字段,则可以使用:IN(语文,英语) 需要说明的是,这里的IN运算符只支持常量,不支持子查询。它默认它的参数均为常量,因此IN(语文,数学,英语),可以写成也可以不写成IN(‘语文’,’数学’,’英语’)。

此外,IN运算符还可以决定列字段的排放顺序,例如IN(语文,数学),在结果表中,'语文'字段在前,'数学'字段在后;而IN(数学,语文),在结果表中,则是'数学'在前,'语文'在后。

 4.

如果我们需要在列字段中增加‘总分’数据,就像透视表那样有一列汇总列,该如何处理呢?

在SELECT子句指定显示的行字段中,使用聚合函数SUM,新增一列求和成绩的记录,并用关键字AS赋以别名‘总分’即可。

语句如下:

TRANSFORM SUM(成绩) SELECT 姓名,SUM(成绩) AS 总分 FROM [成绩表$]  GROUP BY 姓名  PIVOT 学科

查询结果如下:

         

        
如果我们不但需要总分,还需要平均分呢?

大同小异,语句如下:

TRANSFORM SUM(成绩) SELECT 姓名,SUM(成绩) AS 总分,AVG(成绩) AS 平均分 FROM [成绩表$]  GROUP BY 姓名  PIVOT 学科

查询结果如下:

          

 5.

假设我们需要实现以下查询,也就是将姓名划分到各个学科下,并增加一个虚拟的‘班级’字段……

SQL语句该如何编写呢?

           稍加观察,不难发现,上述图片所示的查询结果,不过是把交叉表的,由‘成绩’字段改成了‘姓名’字段,因此我们可以将查询语句写成如下:

TRANSFORM 姓名 SELECT 姓名 FROM [成绩表$]  GROUP BY 姓名  PIVOT 学科

但语句运行后的结果和我们最终的目标稍有不同:

          

交叉表的行字段,也就是‘姓名’字段,是我们所不需要显示的,我们需要显示的是‘班级’。

不知你是否有留意到,前面描述TRANSFORM汉化语法的时候,我们有重点字体加粗标注过,SELECT语句中的GROUP BY子句决定了交叉表实际上的分组行字段,而SELECT子句则决定了交叉表显示的行字段——

因此我们可以将代码修改如下:

TRANSFORM 姓名 SELECT NULL AS 班级 FROM [成绩表$]  GROUP BY 姓名  PIVOT 学科

使用一列NULL值作为交叉表显示的行标题记录,并使用关键字AS赋以别名‘班级’,即可得到我们所需要的查询结果。

 6.

最后,小贴士…… 1),TRANSFORM语句支持WHERE子句,但不支持HAVING子句。 例如我们只需要查询各科成绩大于等于60分的交叉表数据,语句如下:

TRANSFORM SUM(成绩) SELECT 姓名 FROM [成绩表$] WHERE 成绩>=60  GROUP BY 姓名  PIVOT 学科

查询结果如下,只体现了及格学员的成绩:

           2),TRANSFORM查询只能作为结果表存在,或者说,它只能处于查询语句的最外围,而不能嵌套在其它查询语句的内部使用。

相关攻略

  • 给Excel安装监控,专治不按要求填数据

    今天老祝要和大家一起学习一个非常简单,但是十分实用的小技巧。 在下图的员工信息表中,要求各个记录必须是连续输入的,如果输入的不完整或是输入后又删除了记录, Excel 就不..
  • Excel插件:快速制作属于自己Excel插件

    Excel插件:快速制作属于 自己的 插件,建立批量操作工具箱! 工作中处理工作表时常常会遇到一些比较复杂的问题,相信大家都遇到过,比如一键创建工作表目录链接、一键拆分工作..

Copyright 2019-2022 www.vyyoo.com 【微优网】 版权所有

声明:本站部分文章来自互联网 如有异议 请与本站联系 本站为非赢利性网站 不接受任何赞助和广告