这个实例需要用到交叉表查询、联合查询以及嵌套查询，而难点在于行小计、行总计、列小计、列总计。
第一步：考虑交叉表查询。
基本SQL语句：
Transform sum(金额) select 部门类型,供应商部门名称 as 科室 from [Cdata$] group by 部门类型,供应商部门名称 pivot 物资类型
但这条SQL语句只能得到原有数据源表格中的基本数据内容，没有行小计、行总计、列小计、列总计。
如果想把交叉表查询结果作为一个数据源表，然后构建列小计列总计，最后进行多表左连接。本人反复测试，并经linpansheng老师证实，交叉表查询结果不能作为嵌套查询的数据源。所以只能换一种思路才能解决本题，这就是下面第二步所介绍的内容。
第二步：构建这些小计、总计内容。
如何构建这些小计、总计内容呢？必须要从数据源入手，将数据源重新构建，使得构建之后的新数据源有上述小计总计内容。
select 部门类型,供应商部门名称 as 科室,物资类型,sum(金额) as 合计 from [Cdata$] group by 部门类型,供应商部门名称 ,物资类型 union all
select 部门类型,供应商部门名称,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称,物资类别 union all
select 部门类型,供应商部门名称,'总计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称 union all
select 部门类型&'小计','',物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select 部门类型&'小计','',物资类别& '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select 部门类型&'小计','','总计',sum(金额) from [Cdata$] group by 部门类型 union all
select '总计',null,物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select '总计',null,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select '总计',null,'总计',sum(金额) from [Cdata$]
这里面共有九条select查询语句，然后通过联合查询（union all方式联合）将它们组合起来。下面逐条介绍他们的含义（不明白的坛友可以逐条进行测试，便于理解）：
1 得到最终结果表中的白色区域（小计、总计所在列除外）的数据，这个是原始数据源中的基本数据的汇总。
2 得到最终结果表白色区域中的小计所在列数据，即各类各科室的办公费小计、设备小计、专用材料小计。
3 得到最终结果表白色区域中的总计列所在数据，即各类各科室的总计。
4 得到最终结果表中的淡蓝色区域（小计、总计所在列除外）的数据，即ABC类的办公用品、其他材料、设备、纺织品、专用其他材料的小计。
5 得到最终结果表淡蓝色区域中小计所在列的数据，即ABC类的办公用品小计、设备小计、专用材料小计。
6 得到最终结果表淡蓝色区域中总计所在列的数据，即ABC类的总计（即最终结果表中淡蓝色区域的最右边一列）。
7 得到最终结果表区域（小计、总计所在列除外）的数据，即各种物资类型的总计。
8 得到最终结果表区域中小计所在列的数据，即办公用品小计、设备小计、专用材料小计列的总计。
9 得到最终结果表区域中总计所在列的数据，即行总计、列总计交叉的单元格的数据（即最终结果表中的右下角单元格数据）。
总之，要构建最终结果表中的内容，需要将最终结果表根据行字段“ABC类-科室”、“ABC类-小计”、“总计”和列字段“物资类型”、“物资类别小计”、“总计”进行交叉组合，共有33=9个区域的数据，每个区域用一条select语句来生成。并进行union all联合，这样就有了第二步的SQL语句。类似问题可以参照这个模式进行分析，得到解决问题的思路。
第三步：将前面构建后生成的结果作为数据源进行分组查询，使得行列小计总计的数值能进行求和合并。
为了节省篇幅，先假定把上面第二步查询的结果命名为“中间数据表1”，那么这一步的SQL语句如下：
select 部门类型,科室,物资类型,sum(合计)as 合计 from (中间数据表1) group by 部门类型,科室,物资类型
相信这一步大家好理解了。实际SQL语句中，上述的“中间数据表1”就需要用第二步的语句来代替。
第四步：前面第三步的结果作为交叉表查询的数据源，通过行列转置，得到最后的结果。
在EXCEL中，还需要对“总计”所在行进行排序，即下面语句中的( 部门类型='总计')，否则它将位于第一行（ACCESS中不存在此问题，这也是EXCEL和ACCESS的细微区别之一）。第三、四步也是嵌套查询的应用。最终的SQL完整语句如下：，
Transform sum(合计) select 部门类型,科室 from(
select 部门类型,科室,物资类型,sum(合计)as 合计 from
( select 部门类型,供应商部门名称 as 科室,物资类型,sum(金额) as 合计 from [Cdata$] group by 部门类型,供应商部门名称 ,物资类型 union all
select 部门类型,供应商部门名称,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称,物资类别 union all
select 部门类型,供应商部门名称,'总计',sum(金额) from [Cdata$] group by 部门类型,供应商部门名称 union all
select 部门类型&'小计','',物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select 部门类型&'小计','',物资类别& '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select 部门类型&'小计','','总计',sum(金额) from [Cdata$] group by 部门类型 union all
select '总计',null,物资类型,sum(金额) from [Cdata$] group by 部门类型,物资类型 union all
select '总计',null,物资类别 & '小计',sum(金额) from [Cdata$] group by 部门类型,物资类别 union all
select '总计',null,'总计',sum(金额) from [Cdata$]
) group by 部门类型,科室,物资类型
) group by ( 部门类型='总计'),部门类型,科室 order by( 部门类型='总计') desc, 部门类型,科室 pivot 物资类型
相信如果大家能仔细阅读前面每一步的解释，到了最后这一步，只要能理解transform的用法，也就能理解整个这条语句了。至于transform、union all等用法就不是本文的重点，
如何将批量的逐行数据（也叫数据库记录数据）转换成二维交叉表数据，分关键列情况而定，本人举例如下，第一种关键列（关键字段）只有一个的情况：
1、在原表前加一个辅助列 用户ID, 其值来自原用户+序列号（注公式的写法），A2=B2&COUNTIF($B$2:$B2,B2) 下拉；
2、将B列的用户值，复制到 F列（新表），数据---数据工具：删除重复项---得到唯一的用户名（一个用户只出现一次）;
3、对于新表的其他列（G1、H1、I1），名称必须命名为后带序号的列名，如手机号1、手机号2
4、用Vlookup( )函数来查找并匹配值 G2=VLOOKUP($F2&COLUMN( )-6,$A$2:$C$18,3,0),解释：COLUMN()-6就是当前列号7减去6等于1，目的匹配上面对应列的序号 1，且产生出vlookup函数的关键字“查找值”要唯一，将 G2公式竖拉、横拉，当出现 #N/A 值时表示值匹配完毕。
5、根据列字段或行关键值记录的多少，可以做成横向表
G11=VLOOKUP(G$10&ROW()-10,$A$2:$C$18,3,0), G11公式横拉、竖拉，当出现 #N/A 值时表示值匹配完毕。
第二种情况，当关键字段值是由多个列值的组成，请等待几天，本人后续。
今天我也碰到了，一个交叉表查询，在ACCESS2003及2007下正常运行，2010及2016出现该错误提示。因交叉表查询的数据来源也是查询，且带有公式，如果先把来源数据“生成新表”后，再交叉，就能正常，但不符合我的程序要求。后来发现，交叉表的“列标题”字段，一些记录是空值，经交叉统计后，把这些空记录统计在一个<>字段中，问题就在于这个空值，我把数据源查询的空值修改为' '，即带一个空格，而不是''，问题就解决了。下面列出数据源及交叉表查询的SQL,注意数据源也是一个查询,真正的表是T4A，那个空格就是:'上升'后面的' '，交叉的列标题是[变化]
数据源：SELECT T4A, Len([T4A]![组合]) AS tmp, IIf([tmp]>1,IIf(Left([组合],1)<Right([组合],1),'下降',IIf(Left([组合],1)=Right([组合],1),'持平','上升')),' ') AS 变化 FROM T4A;
交叉表查询：TRANSFORM Count(T4BSFZH) AS SFZH之计数 SELECT T4B级, T4B类别, T4BBMD, Count(T4BSFZH) AS [总计 SFZH] FROM T4B GROUP BY T4B级, T4B类别, 4BBMD
PIVOT T4B变化;卡方检验
你的数据应该用交叉列联表做，数据录入格式为：建立两个变量，变量1是组别，
正常对照组用数据1表示，病例组用数据2表示；变量2是疗效等分类变量，用1表示分类属性1，用2表示分类属性2，
还有一个变量3是权重，例数
数据录入完成后，先加权频数后点analyze-descriptive statistics-crosstabs-把变量1选到rows里
，把变量2选到column里，然后点击下面的statistics，打开对话框，勾选chi-squares，
然后点continue，再点ok，出来结果的第3个表就是你要的卡方检验，第一行第一个数是卡方值，
后面是自由度，然后是P值。
SPSS提供了多种适用于不同类型数据的相关系数表达，这些相关性检验的零假设都是：行和列变量之间相互独立，不存在显著的相关关系。根据SPSS检验后得出的相伴概率（Concomitant Significance）判断是否存在相关关系。如果相伴概率小于显著性水平005，那么拒绝零假设，行列变量之间彼此相关；如果相伴概率大于显著性水平005，那么接受原假设，行列变量之间彼此独立。
在交叉列联表分析中，SPSS所提供的相关关系的检验方法主要有以下3种：
（1）卡方（χ2）统计检验：常用于检验行列变量之间是否相关。计算公式为：
其中，f0表示实际观察频数，fe表示期望频数。
卡方统计量服从（行数 1） （列数 1）个自由度的卡方统计。SPSS在计算卡方统计量时，同时给出相应的相伴概率，由此判断行列变量之间是否相关。
（2）列联系数（Contingency coefficient）：常用于名义变量之间的相关系数计算。计算公式由卡方统计量修改而得，公式如下：
（3） 系数（Phi and Cramer's V）：常用于名义变量之间的相关系数计算。计算公式由卡方统计量修改而得，公式如下：
系数介于0和1之间，其中，K为行数和列数较小的实际数。
交叉列联表分析的具体 *** 作步骤如下：
打开数据文件，选择分析（Analyze）菜单，单击描述统计（Descriptive Statistics）命令下的交叉表（Crosstabs）命令。"交叉表"（Crosstabs）主对话框如图3-13所示。
在该主对话框中，左边的变量列表为原变量列表，通过单击 按钮可选择一个或者几个变量进入右边的"行"（Row(s)）变量列表框、"列"（Column(s)）变量列表框和"层"（Layer）变量列表框中。
如果是二维列联表分析，只需选择行列变量即可，但如进行三维以上的列联表分析，可以将其他变量作为控制变量选到"层"（Layer）变量列表框中。有多个层控制变量时，可以根据实际的分析要求确定它们的层次，既可以是同层次的也可以是逐层叠加的。
在"交叉表"对话框底端有两个可选择项：
显示复式条形图（Display clustered bar chart）：指定绘制各个变量不同交叉取值下关于频数分布的柱形图；
取消表格（Suppress table）：不输出列联表的具体表格，而直接显示交叉列联表分析过程中的统计量，如果没有选中统计量，则不产生任何结果。所以，一般情况下，只有在分析行列变量间关系时选择此项。
该对话框的右端有4个按钮，从上到下依次为精确（Exact）按钮、统计量（Statistics）按钮、单元格（Cells）按钮和格式（Format）按钮。单击可进入对应的对话框。
单击精确（Exact）按钮，打开"精确检验"（Exact Tests）对话框，如图3-14所示。
该对话框提供了3种用于不同条件的检验方式来检验行列变量的相关性。用户可选择以下3种检验方式之一：
仅渐近法（Asymptotic only）：适用于具有渐近分布的大样本数据，SPSS默认选择该项。
Monte Carlo（蒙特卡罗法）：此项为精确显著性水平值的无偏估计，无需数据具有渐近分布的假设，是一种非常有效的计算确切显著性水平的方法。在"置信水平"（Confidence Level）参数框内输入数据，可以确定置信区间的大小，一般为90、95、99。在"样本数"（Number of samples）参数框中可以输入数据的样本容量。
精确（Exact）：观察结果概率，同时在下面的"每个检验的时间限制为"（Time limit per test）的参数框内，选择进行精确检验的最大时间限度。
用户在本对话框内进行选择后，单击继续（Continue）按钮即可返回"交叉表"主对话框。一般情况下，"精确检验"（Exact Tests）对话框的选项都默认为系统默认值，不作调整。
单击统计量（Statistics）按钮，打开"交叉表：统计量"（Crosstabs：Statistics）对话框，如图3-15所示。
在该对话框中，用户可以选择输出合适的统计检验统计量。对话框中各选项的意义如下：
（1）卡方（Chi-square）检验复选框：检验列联表行列变量的独立性检验，也被称为Pearson chi-square检验、χ2检验。
（2）相关性（Correlations）检验复选框：输出列联表行列变量的Pearson相关系数或Spearman相关系数。
（3）名义（Nominal）栏：适用于名称变量统计量。
相依系数（Contingency coefficient）：即Pearson相关系数或Spearman相关系数。
Phi 和Cramer变量（ 系数）：常用于名义变量之间的相关系数计算。计算公式由卡方统计量修改而得，如公式（313）所示。ψ系数介于0和1之间，其中，K为行数和列数较小的实际数。
Lambda（λ系数）：在自变量预测中用于反映比例缩减误差，其值为1时表明自变量预测因变量好，为0时表明自变量预测因变量差。
不定性系数（Uncertainty coefficient）：以熵为标准的比例缩减误差，其值接近1时表明后一变量的信息很大程度上来自前一变量，其值接近0时表明后一变量的信息与前一变量无关。
（4）有序（Ordinal）栏：适用于有序变量的统计量。
Gamma（伽马系数，γ系数）：两有序变量之间的关联性的对称检验。其数值界于0和1之间，所有观察实际数集中于左上角和右下角时，取值为1，表示两个变量之间有很强的相关；取值为0时，表示两个变量之间相互独立。
Somers'd值：两有序变量之间的关联性的检验，取值范围为[-1，1]。
Kendall s tau-b值：考虑有结的秩或等级变量关联性的非参数检验，相同的观察值选入计算过程中，取值范围为[-1，1]。
Kendall s tau-c值：忽略有结的秩或等级变量关联性的非参数检验，相同的观察值不选入计算过程，取值范围界为[-1，1]。
（5）按区间标定（Nominal by interval）栏：适用于一个名义变量与一个等距变量的相关性检验。
Kappa系数：检验数据内部的一致性，仅适用于具有相同分类值和相同分类数量的变量交叉表。
Eta值：其平方值可认为是因变量受不同因素影响所致方差的比例。
风险（相对危险度）：检验事件发生和某因素之间的关联性。
McNemar检验：主要用于检验配对的资料率（相当于配对卡方检验）。
（6）Cochran's and Mantel-Haenszel统计量复选框：适用于在一个二值因素变量和一个二值响应变量之间的独立性检验。
用户在"交叉表：统计量"对话框中进行选择后，单击继续（Continue），即可返回"交叉表"（Crosstabs）主对话框。一般情况下，对"交叉表：统计量"对话框内的选项不作选择或选择较为常用的卡方检验。
单击单元格（Cells）按钮，打开"交叉表：单元显示"（Crosstabs：Cell Display）对话框，如图3-16所示。
在该对话框中，用户可以指定列联表单元格中的输出内容。SPSS170默认在交叉列联表中输出实际的观察值，但观察值有时候不能确切地反映事物的实质，因此还需要输出其他的数据项。对话框中各选项的具体意义如下：
（1）计数（Counts）栏：
观察值（Observed）：系统默认选项，表示输出为实际观察值。
期望值（Expected）：表示输出为理论值。
（2）百分比（Percentages）栏：
行（Row）百分比：以行为单元，统计行变量的百分比。
列（Column）百分比：以列为单元，统计列变量的百分比。
总计（Total）百分比：行列变量的百分比都进行输出。
（3）残差（Residuals）栏：
未标准化（Unstandardized）：输出非标准化残差，为实际数与理论数的差值。
标准化（Standardized）：输出标准化残差，为实际数与理论数的差值除以理论数。
调节的标准化（Adjusted standardized）：输出修正标准化残差，为标准误确定的单元格残差。
（4）非整数权重（Noninteger Weights）栏：
四舍五入单元格计数（Round cell counts，系统默认）：将单元格计数的非整数部分的尾数四舍五入为整数。
截短单元格计数（Truncate cell counts）：将单元格计数的非整数部分的尾数舍去，直接化为整数。
四舍五入个案权重（Round case Weights）：将观测量权数的非整数部分的尾数四舍五入为整数。
截短个案权重（Truncate case Weights）：将观测量权数的非整数部分的尾数舍去，化为整数。
无调节（No adjustments）：不对计数数据进行调整。
用户在"交叉表：单元显示"对话框中进行选择后，单击继续（Continue）按钮，即可返回"交叉表"主对话框。一般情况下，对"交叉表：单元显示"对话框的选项都默认为系统默认值，不作调整。
单击格式（Format）按钮，打开"交叉表：表格格式"（Crosstabs：Table Format）对话框，如图3-17所示。
在该对话框中，用户可以指定列联表的输出排列顺序。对话框中各选项的具体意义如下：
在行序（Row Order）栏中有如下两个选项：
升序（Ascending）：系统默认，以升序显示各变量值；
降序（Descending）：以降序显示各变量值。
用户在该对话框中进行选择后，单击继续（Continue）按钮，即可返回"交叉表"主对话框。
在"交叉表"对话框中单击确定（OK）按钮，可在输出窗口中得到数据概述、交叉列联表、卡方检验表、交叉分组下频率分布柱形图、相对危险性估计等图表。
假设ABCG列位于同一个Sheet1表，需要在Sheet2表中进行汇总，那么在Sheet2的B3单元格输入以下公式，然后向右向下填充公式
=COUNTIFS(Sheet1!$G:$G,B$2,Sheet1!$B:$B,$A3)
E3单元格输入以下公式，然后向右向下填充公式
=COUNTIFS(Sheet1!$G:$G,B$2,Sheet1!$C:$C,$A3)
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）