|
|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
【亮点】 由于之前从没见到哪位老师介绍在一张表格中使用SQL语句直接生成行小计、列小计、行总计、列总计，所以这应该是本人的首创。
【背景说明】这是本人在EH论坛中看到的问题然后费尽心思，花了一个晚上的时间终于有了解决方案，后来坛友nectar根据linpansheng老师的帖子，提出了改进建议，解决了“总计”所在行的位置问题，也让我学习到了针对个别数据项的特别排序方法，在此对两位表示衷心的感谢！也在此介绍出来，供大家分享，但愿能给学习SQL的初学者带来一些帮助，提供一定的参考价值。当然也希望能有老师给予指点。
（引用问题所在原始帖子：http://club.excelhome.net/viewth ... p;page=1#pid3887787）
数据源“Cdata”工作表的主要内容如下（表格列数较多，只截取相关的内容）：
期望的最终结果：
提问者本来希望是ACCESS中解决，我的看法是：EXCEL中编写的SQL语句只需对个别细节做修改即可移植到ACCESS中，我主要是学习EXCEL中的SQL用法，所以这里就以EXCEL-SQL的解决方法来进行比较详细的介绍。
这个实例需要用到交叉表查询、联合查询以及嵌套查询，而难点在于行小计、行总计、列小计、列总计。
第一步：考虑交叉表查询。
基本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类-小计”、“总计”和列字段“物资类型”、“物资类别小计”、“总计”进行交叉组合，共有3*3=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等用法就不是本文的重点，有需要了解的坛友请搜索参考几位老师的介绍，EH论坛中挺多的。
交叉表查询、联合查询、嵌套查询的综合应用实例.rar
(9.12 KB, 下载次数: 1316)
[ 本帖最后由 whsfhwm 于 2010-5-30 18:07 编辑 ] |
评分
-
4
查看全部评分
-
|
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-5-30 17:58
楼主
