……在日落的海边 在热闹的大街……
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查询只能作为结果表存在，或者说，它只能处于查询语句的最外围，而不能嵌套在其它查询语句的内部使用。
图文作者：看见星光
联系客服
