转载

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

你真的会玩SQL吗？之逻辑查询处理阶段

你真的会玩SQL吗？和平大使内连接、外连接

你真的会玩SQL吗？三范式、数据完整性

你真的会玩SQL吗？查询指定节点及其所有父节点的方法

你真的会玩SQL吗？让人晕头转向的三值逻辑

你真的会玩SQL吗？EXISTS和IN之间的区别

你真的会玩SQL吗？无处不在的子查询

你真的会玩SQL吗？Case也疯狂

你真的会玩SQL吗？表表达式，排名函数

你真的会玩SQL吗？简单的数据修改

你真的会玩SQL吗？你所不知道的数据聚合

你真的会玩SQL吗？透视转换的艺术

你真的会玩SQL吗？冷落的Top和Apply

你真的会玩SQL吗？实用函数方法汇总

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

本系列之前的所有知识均为本章作准备，若看不懂本章可先回头温习下之前的系列。在之前还是先提一下中心思想： SQL数据处理是集合思维，不要用逻辑思维来思考。

在项目中经常需要从基础数据中提取 数据进行处理 后显示给老板或客户一些报表，这时数据量大，涉及表多，简单的表处理SQL无法满足，且需要重复使用，这时就要使用存储过程来处理大数据和 复杂的业务逻辑 。可能会有人提出在后台读出DataSet加载到内在中用逻辑来处理，但之前说过， 逻辑处理远远没有数据库集合处理快 ，且占用了宝贵的内存，运用好可以减少网络流量、可提高数据库系统的安全性。

存储过程的编写最重要的是 思路清晰 ，能知道自己想要的结果和写出的SQL能运行出什么样的结果，这需要基本功非常扎实，过程中会用到 联表查询、更新、临时表、数据聚合、行列转换、简单的函数 ……等知识。

接下来不多说，直接上需求实例：

需求：统计某个项目下各个产品的具体销售情况

数据表：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

表结构如下：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

由于数据库数据经过翻倍，数据库用例数据数量有点大，请要下载的可以在此下载，然后自行 还原数据库 ，传送门： 链接： http://pan.baidu.com/s/1o6MUnay 密码：swi4

需要经过编写SQL显示数据库中销售记录是按 每个产品、每月一条记录 存储的，需要展示如下图。

总的显示 一个项目 ，然后再按每个产品进行分组展示，每个产品有 7个属性行统计数据 ， 再将所有产品分别进行合计，放到各自产品上面 。

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

部分业务名词解释：

横项 总项目 数据= 产品1 + 产品2 + 产品3 + 产品4……

“项目合计”列：整个项目的，即以前年度合计+2011年合计+以后年度合计。如产品1的“销售套数”的项目合计 5555 = 2011 前年度合计 3030 + 指定查询年2011年的 505 + 2011年以后的 2020 ，销售面积等以此类推。

“以前年度合计”列：2011年以前所有年的合计。

“以后年度合计”列：2011年以后所有年的合计。

“累计”，统计所在周期+以前合计，如2011-01累计销售面积指2011-01年以前（ 包括2011-01 ）的销售面积之和。

“累计销售比例”：累计销售面积/项目总销售面积。如产品1 的2011-02的“累计销售面积比例” 0.55 = 2011-02的“累计销售面积” 127200.00 / 产品1的项目合计的 “累计销售面积” 229900.00

“累计销售面积”：到当前统计时间为止的所有销售面积，如产品1的2011-02月的“累计销售面积” 127200.00 = 2011-1月"累计销售的面积" 126400.00 + 2011-02月的“销售面积“ 800.00 ，其它概念以此类推。

在这里有个特别的是 “累计销售面积”的”以后年度合计“ ，如产品1的 2011年的 ”以后年度合计“ 的 “累计销售面积” 229900.00 = 2011年的 “累计销售面积” 146300.00 + 2011年后的 ”销售面积” 83600.00 ，在这里你会发现229900和产品1的项目合计的“累计销售面积”相同，这个是正确的，项目合计中的累积面积并不等于以前年+当年+以后年，请理解一下这个滑动聚合概念。

其中需要传入两个参数： 项目ID 和年份

下面来理一理整体的思路：

如果只统计一个产品显示以上的数据该如何写呢？你可以先试一下。

先将数据表拆分：

横向：总项目合计+ 每个产品中每个子项（如销售套数，销售面积等）+每个产品累计销售面积

竖向：项目、产品基本信息+当年每月各项累积+以前年度合计+以后年度合计+项目总合计

核心数据表：销售明细表，进行分析核心数据列：销售面积、销售均价、销售金额

核心操作：行、列互转，滑动聚合统计

由于涉及到的知识过于庞大，流程过于繁多，导致整个篇幅过长，因此在这里分为上、下篇来讲解。

那来看看整个流程思路，先过滤数据：

1. 查找该项目的 所有产品 放进临时表A
2. 查找该项目的所有产品的 销售明细 放进临时表B
3. 从临时表B中查找 指定年的销售明细 放进临时表C
4. 从表C统计 当前年度合计列 ，各产品的所有面积、金额、均价总合计放入表C
5. 从表B统计 以前年度 的各产品的所有面积、金额、均价总合计操作与上一步类似放入表C
6. 从表B统计 以后年度 的各产品的所有面积、金额、均价总合计操作与上一步类似放入表C
7. 从表B统计各产品取 所有的合计 放入表C
8. 从表C统计 累积销售面积、累积销售面积比例，累积销售金额 更新表C
9. 从表C 列转行 ，转换后的表只有产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值) 放入表D
10. 从表D 行转列 ，按类型聚合求出每个产品每个类型(面积、金额……)的合计放入表E
11. 从表E 联接产品表A 与敷项目表查询出最后的显示

以上只是大概思路，过程中会讲一些技巧。

/*临时表说明   #product：用项目过滤后，将“合计”作为一个产品的集合   #TempAllSaleDtl：通过项目过滤后的销售明细，所有月的   #ProductSaleArea：各个产品的总面积，用于计算比例   #TempSaleDtl：通过日期过滤，且加工过后的销售明细，包括增加累积列，以前年度、以后年度、项目合计的记录   #tempSaleDtl2：列转行后的数据集   #tempSaleDtl3：行转列后的数据集    */

设置要查询的参数，以下示例为了好说明，特用 2011年作统一说明 ：

DECLARE @ProjectGUID UNIQUEIDENTIFIER SET @ProjectGUID='8FA659C8-3DA9-4330-B277-9B517E67606D'--要查询的项目 DECLARE @Year CHAR(4) SET @Year='2011'--要统一的年份

查找该项目的 所有产品 放进临时表#product，这里将“合计”作为一个产品的集合也插入产品表#product：

select ProductGUID,ProductName,ProjectGUID,ProductCode into #product from(     select ProductGUID,ProductName,ProjectGUID,ProductName as ProductCode from Product where ProjectGUID=@ProjectGUID     union all     select '00000000-0000-0000-0000-000000000000','合计',@ProjectGUID,'00' as ProductCode ) a

查找该项目的所有产品的 销售明细 放进临时表#TempAllSaleDtl，以作备用：

SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice INTO #TempAllSaleDtl  FROM dbo.SaleDtl WHERE ProductGUID IN (     SELECT ProductGUID FROM dbo.Product WHERE ProjectGUID=@ProjectGUID  )

根据#TempAllSaleDtl现有数据统计，向#TempAllSaleDtl添加 总合计记录

--根据现有数据统计，向#TempAllSaleDtl添加总合计记录 insert into #TempAllSaleDtl(ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice) select '00000000-0000-0000-0000-000000000000',YearMonth,SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea)  from #TempAllSaleDtl group by YearMonth

添加的部分数据如图：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从临时表#TempAllSaleDtl 中查找 指定年 的销售明细放进临时表#TempSaleDtl ，注意这个时候就已经包含了 “合计”产品00的数据：

--查找某年的销售明细：#TempSaleDtl SELECT ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,  SalePrice AS ljSaleArea,  SalePrice AS blSaleArea,  SalePrice AS ljSaleAmount INTO #TempSaleDtl  FROM #TempAllSaleDtl  WHERE LEFT([YearMonth],4)=@Year

从#TempAllSaleDtl 中统计项目各个产品的总销售面积放入表：#ProductSaleArea，主要用作计算 项目累计销售面积比例

--获取项目各个产品的总销售面积：#ProductSaleArea SELECT ProductGUID,SUM(SaleArea) AS all_SaleArea INTO #ProductSaleArea  FROM #TempAllSaleDtl GROUP BY ProductGUID

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从表#TempSaleDtl 统计 当前年度合计列 ，各产品的所有面积、金额、均价总合计放入表#TempSaleDtl，注意这里 SUM(SaleAmount)/SUM(SaleArea) 计算销售单价：

--添加2011合计列的记录(本年度的各产品的所有面积、金额、均价总合计) insert into #TempSaleDtl(     ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount) select ProductGUID,@Year+'-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0  from #TempSaleDtl group by ProductGUID

部分数据如图，这里有个技巧是用 2011-13代表2011整个年份：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从表#TempAllSaleDtl 统计 以前年度 的 各产品 的所有面积、金额、均价总合计操作与上一步类似放入表#TempSaleDtl

--以前年度列记录（本年度以前的各产品的所有面积、金额、均价总合计 操作与上一步类似） insert into #TempSaleDtl(     ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount) select ProductGUID,@Year+'-00',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0  from #TempAllSaleDtl where YearMonth <@Year+'-00' group by ProductGUID

部分数据如图，这里有个技巧是用 2011-00代表2011年以前年份：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从表#TempAllSaleDtl 统计 以后年度 的 各产品 的所有面积、金额、均价总合计操作与上一步类似放入表#TempSaleDtl

--以后年度列记录（本年度以后的各产品的所有面积、金额、均价总合计 操作与上一步类似） insert into #TempSaleDtl(     ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount) select ProductGUID,'9999-12',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0  from #TempAllSaleDtl where YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00' group by ProductGUID

部分数据如图，这里有个技巧是用 9999-12代表2011年以后年份：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从表#TempAllSaleDtl 统计 各产品取所有的合计 放入表#TempSaleDtl

--项目合计列记录(各产品取所有的合计。与上面的区别在于没有添加 here YearMonth >cast((cast(@Year as int) +1) as CHAR(4))+'-00') insert into #TempSaleDtl(     ProductGUID,YearMonth,SaleAmount,SaleArea,SaleNum,SalePrice,ljSaleArea, blSaleArea,ljSaleAmount) select ProductGUID,'9999-13',SUM(SaleAmount),SUM(SaleArea),SUM(SaleNum),SUM(SaleAmount)/SUM(SaleArea),0,0,0  from #TempAllSaleDtl group by ProductGUID

部分数据如图，这里有个技巧是用 9999-13代表所有年份：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

以上数据中我们的累积销售面积、累积销售面积比例，累积销售金额三项之前都用0代替，现在我们来统计。

从表#TempSaleDtl 与 #TempAllSaleDtl统计 累积销售面积、累积销售面积比例，累积销售金额 更新表#TempSaleDtl

--更新销售明细TempSaleDtl的累积销售面积、累积销售面积比例，累积销售金额 UPDATE #TempSaleDtl SET   ljSaleArea=b.sum_SaleArea,  ljSaleAmount=b.sum_SaleAmount,  blSaleArea=b.sum_SaleArea/c.all_SaleArea FROM #TempSaleDtl left JOIN (  SELECT n.ProductGUID,n.YearMonth,SUM(m.SaleArea) AS sum_SaleArea,SUM(m.SaleAmount) AS sum_SaleAmount   FROM #TempAllSaleDtl m  INNER JOIN #TempSaleDtl n ON m.YearMonth<=n.YearMonth AND m.ProductGUID=n.ProductGUID  GROUP BY n.ProductGUID,n.YearMonth  ) b ON #TempSaleDtl.ProductGUID=b.ProductGUID AND #TempSaleDtl.YearMonth=b.YearMonth LEFT JOIN #ProductSaleArea c ON c.ProductGUID=#TempSaleDtl.ProductGUID

注意这里用到了滑动累计聚合 m.YearMonth<=n.YearMonth（不懂 滑动累计聚合 请看 之前的 系列），利用子查询统计出 每个产品到当月为止 的累计销售面积，累积销售金额，再联接 #ProductSaleArea 更新每个产品的累积销售面积比例。

部分数据如图：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）

从表#TempSaleDtl 列转行，转换后的表只有产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值) 放入表#tempSaleDtl2

--列转行，转换后的表只有 产品、统计类型、日期，值4列；(每个产品对应的0-12、13 月对应的值) SELECT * INTO #tempSaleDtl2 FROM (  SELECT ProductGUID,'销售套数' AS type,'01' AS typecode,YearMonth,MAX(SaleNum) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售面积' AS type,'02' AS typecode,YearMonth,MAX(SaleArea) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售均价' AS type,'03' AS typecode,YearMonth,MAX(SalePrice) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'销售金额' AS type,'04' AS typecode,YearMonth,MAX(SaleAmount) AS val FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售面积' AS type, '05' AS typecode,YearMonth,SUM(ljSaleArea) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售面积比例' AS type, '06' AS typecode,YearMonth,SUM(blSaleArea) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth  UNION ALL  SELECT ProductGUID,'累计销售金额' AS type, '07' AS typecode,YearMonth,SUM(ljSaleAmount) FROM #TempSaleDtl  GROUP BY ProductGUID,YearMonth ) t

部分数据如图：

你真的会玩SQL吗？玩爆你的数据报表之存储过程编写（上）