利用SQL创建动态数据透视表统计查询进销存状况

楼主 lrlxxqxa	一、背景简述：随着信息技术的飞速发展，对企业的进销存动态管理也不断提出高度个性化的要求，在商品的购销链各个环节，经常需要利用Excel统计查询进销存状况，但源数据往往放置在多张工作表中，且结构和字段名称都有所差异；快捷的创建动态查询，即时依据源数据变动反映进销存状况，不但能够帮助业务人员提高工作效率，而且能满足管理者的个性化需求，做出及时准确的决策。本文就结合实例来介绍几种库存管理查询的数据处理方法，并对各种方法进行对比阐述优缺点及需要注意的事项。二、提出问题：数据源工作簿中包含期初库存、本期收入和本期发出3张工作表，分别记录了各经销商和物料的进销存明细，但各张工作表结构不完全一致，且字段名称不同，如何在此基础上准确的对多字段下各项数据进行分类并统计呢？三、分析问题：首先观察源数据结构，各源表结构如下：期初库存表：本期收入表：本期发出表：明确目的是统计各物料的收发、结存情况（期初库存、本期收入、本期发出、期末结存）后，联想到两点： 1、SQL语句中的查询、更新和管理数据功能，联合和嵌套查询使其具有极大的灵活性和强大功能； 2、数据透视表的友好交互界面，依据需求动态的改变版面布局，按照不同方式分析处理数据，运算快捷且便于更新。基于此两点，确定解决问题的思路是双剑合璧：利用SQL语句创建动态数据透视表。四、解决问题：本文给出3种解法，概述如下： 1、方法一、利用SQL语句调取少量字段并创建新字段“标识”，将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值，然后创建动态数据透视表，最后在透视表中创建计算项进行计算； 2、方法二、利用SQL语句调取全部字段并创建新字段“标识”，将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值，然后创建动态数据透视表，依据需求拖入需要的字段进入行标签，最后在透视表中创建计算项进行计算； 3、方法三、利用SQL语句创建独立的“期初库存”、“本期收入”和“本期发出”字段，然后创建动态数据透视表，拖入全部字段进入行标签，最后在透视表中创建计算字段进行计算；下面分别在2至4楼详细解析各种方法，5楼对比分析并总结。 lrlxxqxa的微博库存表（原始）.rar
2楼 lrlxxqxa	方法一、利用SQL语句调取少量字段并创建新字段“标识”，将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值，然后创建动态数据透视表，最后在透视表中创建计算项进行计算；需要注意的是SQL语句中的各字段名称要和源表中一致，若字段中含有空格要用[]括起来，如本期收入工作表中的“ 数量 ”需要写为[ 数量 ] select 供應商,物料名稱,數量, "期初库存" as 标识 from [期初庫存$] union all select 供應商,貨物名稱,[ 數量 ], "本期入库" as 标识 from [本期收入$] union all select 供慶商,物料名稱,數量, "本期出库" as 标识 from [本期發出$]] 依次点击“数据”--》“现有连接”--》“浏览更多”--》选择位置和工作簿创建数据透视表后，建立计算项“期末库存” =期初库存 +本期入库 -本期出库 2010版本下操作过程如下： 2003版本下操作过程如下：库存表.rar
3楼 lrlxxqxa	方法二、利用SQL语句调取全部字段并创建新字段“标识”，将源数据中的期初库存、本期收入和本期发出数量给“标识”字段赋值，然后创建动态数据透视表，依据需求拖入需要的字段进入行标签，最后在透视表中创建计算项进行计算； select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 数量, "期初库存" as 标识 from [期初庫存$] union all select 供應商,倉別,類別,貨物名稱 as 物料名稱,規格,顏色,單位,[ 數量 ] as 数量, "本期入库" as 标识 from [本期收入$] union all select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 数量, "本期出库" as 标识 from [本期發出$] 创建数据透视表后，建立计算项“期末库存” =期初库存+本期入库 -本期出库注意：基于这个SQL语句创建的数据结构，当添加行标签少的时候运算很快，但当不断填入行标签时，就会使右边的数据区记录条数猛增，虽然数据源表只有几条数据，但添加5个字段进入行标签的时候已超过2万数据，当添加第6个行标签时，会出现“数据透视表在工作表上安排不下。是否要尽可能显示？”的提示（如下）继续确定依然可以执行运算，但因为重复的字段一一比对运算产生了笛卡尔乘积。当加入第7个行标签的时候会出现卡死的现象，导致运算无法完成。所以此种方法只适用于要求显示少数行标签时使用。布局如下：关于此方法的操作及遇到的提示动画如下：
4楼 lrlxxqxa	方法三、利用sql语句创建独立的“期初库存”、“本期收入”和“本期发出”字段，然后创建动态数据透视表，拖入全部字段进入行标签，最后在透视表中创建计算字段进行计算； select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 期初库存,0 as 本期入库,0 as 本期出库 from [期初庫存 $] union all select 供應商,倉別,類別,貨物名稱,規格,顏色,單位,0 as 期初库存,[ 數量 ] as 本期入库,0 as 本期出库 from [本期收入$] union all select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,0 as 期初库存,0 as 本期入库,數量 as 本期出库 from [本期發出$] 创建数据透视表后，添加计算字段“期末库存” =期初库存+本期入库 -本期出库这样创建的数据透视表更加灵活，布局如下：上述代码可简化为如下形式。sql中结构相同的多语句并联，缺省字段名称时默认对应前面的字段，也就是会顺序使用第一个select语句中的相应位置的字段名。 select 供應商,倉別,類別,物料名稱,規格,顏色,單位,數量 as 期初库存,0 as 本期入库,0 as 本期出库 from [期初庫存$] union all select 供應商,倉別,類別,貨物名稱,規格,顏色,單位,0,[ 數量 ] as 本期入库,0 from [本期收入$] union all select 供慶商,倉別,類別,物料名稱,規格,顏色,單位,0,0,數量 as 本期出库 from [本期發出$] 库存表（改进）.rar
5楼 lrlxxqxa	对比分析方法1和方法2都是通过新建一个“标识”字段，并将每种库存信息作为该字段的内容填充，最后通过创建数据透视表计算项来计算期末库存。方法1只利用SQL调取必要字段创建数据透视表，无法呈现其他字段信息；方法2虽然利用SQL调取了所有的字段创建数据透视表，但行标签的数量受限制。方法3是将每种库存信息提取出来并新建一个字段，最后通过创建数据透视表计算字段来计算期末库存。此方法创建出来的数据透视表布局不受限制，能够更加灵活的根据需求变换数据展现方式。
6楼野恋	努力学习,谢谢楼主.
7楼冰心8549	谢谢分享，学习学习
8楼 qjwdw29	不错，支持下**
9楼 shxumin	谢谢分享，好好学习
10楼 larkzh	收藏了。
11楼滩阿园璇	网络推广\|社区推广\|广告群发\|关键字优化\|送200帖承接网络推广业务社区广告群发 qq 229 7997691（注明网络推广否则不加） 40元 1000帖完成后发工作量 excel 给您带着所有发帖的地址问：1000个帖子是多少个社区啊？答：1000个不同的社区问：是发帖还是回帖？答：都可以问：人工发还是机器发的啊？答：机器发。问：可不可以我指定社区？答：不可以社区不挑全国海发问：可以是我当地地区的社区吗？答：不可以只针对全国业务的。问：发了以后会删帖吗？答：删帖是管理员说的算，不是我们说的算的，肯定会有删帖，所以说1000帖，我们都会多发一点作为损耗。问：删帖了你们给补吗？答：不补。问：可不可以先试用一下？好的话我就付款推广。答：不可以。优惠活动进行中！付款400 元推广12天每天1000帖付款800元推广25天每天1000帖付款1000元推广 30天每天1200帖全国社区海发可带外链先付款后发帖！也可以走淘宝但是必须是淘宝账户信誉很高的 1个钻以上的信誉不高的就先打款 qq 229 7997691（注明网络推广否则不加）第一次合作一次支付10天以上的可以送200帖请仔细看帖子内容关于价格跟工作量的加上qq以后再问“价格多少怎么收费” “我怎么知道你发没发” "删帖怎么办"这样问题的直接拉黑
12楼 yardview	谢谢分享，好好学习
13楼 pkpkyb	好的好我正学
14楼薄凇葆三	顶．支持，路过！
15楼 wenzhimu	真棒
16楼 whangsong	以后多多交流, 向大家学习
17楼 chx95love	好东东啊，学习下
18楼 ybc76	收藏学习了，不错
19楼 boxind	学习了
20楼飞来飞去的	这个例子的前提是以“一个月”作为前提的：期初、期末都是当月，请问如何变成一年的呢？要求还能够分月统计每个月的进销存情况
21楼 lianzi2	好
22楼 lianzi2	好
23楼杂草精神	太有用了
24楼 Pak	谢谢分享，好好学习下