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

你真的会玩SQL吗？系列目录

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 

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

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

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

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

 

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

数据表：

表结构如下：

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

 

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

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

 部分业务名词解释：

       横项 总项目数据= 产品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

添加的部分数据如图：

 

 从临时表#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

 

 

从表#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整个年份：

 

从表#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年以前年份：

 

从表#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年以后年份：

 

从表#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代表所有年份：

 

以上数据中我们的 累积销售面积、累积销售面积比例，累积销售金额三项 之前都用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 更新每个产品的累积销售面积比例。

部分数据如图：

 

从表#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

部分数据如图：

这里用到的列转行，共有7列，技巧为用code来代表每个类型，也用于显示排序，最终数据为每个产品每个月都有7行数据。这里是不是有了最终结果的雏形？

 　　

　　至此 你真的会玩SQL吗？玩爆你的数据报表之存储过程编写 上篇先写到这，对于看不懂的建议先建立数据库，然后自己一步步试着理下思路，试着写。

这里留个作业，如何将上面的数据转化为下图中的格式呢?

 SQL类下载资源已放入公众号【一个码农的日常】 ，回复：数据库 即可，今后会不定期更新

敬请期待下篇，未完待续……

posted @ 2015-11-19 08:42  欢醉  阅读(16975)  评论(18)  编辑  收藏  举报