你真的會玩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類下載下傳資源已放入公衆号【一個碼農的日常】，回複：資料庫即可，今後會不定期更新

敬請期待下篇，未完待續……

作者：歡醉

公衆号【一個碼農的日常】技術群：319931204 1号群： 437802986 2号群： 340250479

出處：http://zhangs1986.cnblogs.com/

碼雲：https://gitee.com/huanzui

本文版權歸作者和部落格園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接配接，否則保留追究法律責任的權利。

Top

你真的會玩SQL嗎？玩爆你的資料報表之存儲過程編寫（上）

繼續閱讀

oracle中的start with connect by用法

JDBC連接配接資料庫（statement）

MyBatis-Plus 之AR模式

ASP程式設計經典例子

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離