社群
學習
工具庫
AI工具
休閒
搜尋
繁体中文
目錄
                 在Sqlserver下巧用行列转换日期的数据统计
前言
  1.1 表业务逻辑 
 1.2 项目的需求
   2.准备的基础表
    2.1 合同信息表 
    2.2 合同行项目表
 2.3 合同固定排期表
    3.补充其他（待）
实现思路
留下的思考
感谢
首頁 資料庫 mysql教程 在Sqlserver下巧用行列转换日期的数据统计

在Sqlserver下巧用行列转换日期的数据统计

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:25 PM
sql sqlserver 數據統計 日期 轉換

在Sqlserver下巧用行列转换日期的数据统计 前言 在SQLSERVER中有很多统计函数的基础语法，有使用Group By或partition by后配合Sum,Count(*)等用法。常应用于统计网站的PV流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互

                 在Sqlserver下巧用行列转换日期的数据统计

 

前言

    在SQLSERVER 中有很多统计函数的基础语法，有使用Group By 或 partition by 后配合Sum,Count(*) 等用法。常应用于统计网站的PV流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互相学习下：） 

背景 

       合同中行项目按月收入的统计

  1.业务逻辑及需求 

  1.1 表业务逻辑 

    合同是公司间互相签署的法律契约，一份合同从诞生起，就开始流转于公司的各个部门，最核心的还是盈亏的数值。盈亏是结果，数据的产生源于每个自然月或其他时段的汇总。 往往在实际业务中，例如有些广告行业，立项是分为固定排期和合同活动收入。  

   固定排期一般以一个自然月为周期，例如[201503,201504]间产生的预收入；活动收入表中的活动是指收入周期不固定，可能ConfirmDate  发生在一个月中的若干天中，也可能在间隔一个月后发生。

   无论是固定排期还是活动收入都和行项目有关，行项目是一个编号，一个行项目可以对应多次排期或活动收入的统计,在我给大家介绍的Demo中，将暂时考虑固定排期的情况。

 1.2 项目的需求

   统计合同中行项目的金额：分为结转金额数据汇总，和按自然月条件下金额的汇总。

 

   2.准备的基础表

 

    2.1 合同信息表 

<span>CREATE</span> <span>TABLE</span>  ContractInfo <span>--</span><span>基本信息表</span>
<span>(
</span><span>[</span><span>ContractCode</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>Primary</span> <span>key</span><span>
,</span><span>[</span><span>CustomName</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>100</span>) <span>NULL</span><span>,
)

</span><span>insert</span> <span>into</span><span> ContractInfo
(ContractCode,CustomName)
</span><span>values</span>(<span>'</span><span>30100013000861</span><span>'</span>,<span>'</span><span>弘化四方</span><span>'</span><span>)
    ,(</span><span>'</span><span>30100013000862</span><span>'</span>,<span>'</span><span>明心见性</span><span>'</span><span>)
    ,(</span><span>'</span><span>30100013000863</span><span>'</span>,<span>'</span><span>心绽莲花</span><span>'</span>)
登入後複製

    2.2 合同行项目表

<span>CREATE</span> <span>TABLE</span> ContractLine <span>--</span><span>合同行项目表</span>
<span>(
  </span><span>[</span><span>LineID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>Primary</span> <span>Key</span> <span>NOT</span> <span>NULL</span><span>,
  </span><span>[</span><span>ContractCode</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NOT</span> <span>NULL</span><span>,  
)

</span><span>insert</span> <span>into</span><span> ContractLine
(ContractCode)
</span><span>values</span>(<span>'</span><span>30100013000861</span><span>'</span><span>)
      ,(</span><span>'</span><span>30100013000862</span><span>'</span><span>)
      ,(</span><span>'</span><span>30100013000862</span><span>'</span><span>) 
      ,(</span><span>'</span><span>30100013000863</span><span>'</span><span>)
      ,(</span><span>'</span><span>30100013000863</span><span>'</span>)
登入後複製

 2.3 合同固定排期表

<span>CREATE</span> <span>TABLE</span> ContractSchedule  <span>--</span><span>合同固定排期表(</span>
  <span>[</span><span>ScheduleID</span><span>]</span> <span>[</span><span>int</span><span>]</span>  <span>Primary</span> <span>key</span>  <span>NOT</span> <span>NULL</span><span>,-- 排期ID
    </span><span>[</span><span>LineID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, -- 行项目ID
    </span><span>[</span><span>Period</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, --时间段
    </span><span>[</span><span>Amount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NOT</span> <span>NULL</span><span>, --交易金额
)

</span><span>insert</span> <span>into</span><span> ContractSchedule
(ScheduleID,LineID,Period,Amount)
</span><span>values</span><span>
(</span><span>89106</span>,<span>1</span>,<span>201507</span>,<span>90900.00</span><span>)
,(</span><span>89107</span>,<span>1</span>,<span>201508</span>,<span>9453.00</span><span>)
,(</span><span>89108</span>,<span>1</span>,<span>201510</span>,<span>13000.00</span><span>)
,(</span><span>89109</span>,<span>2</span>,<span>201501</span>,<span>12000.00</span><span>)
,(</span><span>89110</span>,<span>2</span>,<span>201503</span>,<span>11000.00</span><span>)
,(</span><span>89111</span>,<span>3</span>,<span>201509</span>,<span>9000.00</span><span>)
,(</span><span>89112</span>,<span>4</span>,<span>201510</span>,<span>8500.00</span>)
登入後複製

 

    3.补充其他（待）

 

基础知识点：

   1.FOR XML PATH  //用于统计时转换行列的格式，

   参考：王波洋老师的 灵活运用 FOR XML PATH

   2.PIVOT (SUM(Amount)) For Period //用于基础表基础上的行列转换，

   参考：大志若愚老师的 纵表、横表互转的SQL

   3.Select SUM(Amount) From ContractSchedule

    group by LineID // 根据条件汇总数据

   

实现思路

 

 逻辑 

/*计算时间的基础序列*/ ->/*格式化日期序列*/ -> /*关联逻辑表，查询计算8月份之前的汇总,8月份之后的按月份统计*/

 

代码片段

<span> 1</span> <span>/*</span><span>---------------计算时间的基础序列------------</span><span>*/</span>
<span> 2</span> 
<span> 3</span> <span>/*</span><span>获取日期序列起始值</span><span>*/<br>    <span>DECLARE @sdate CHAR(10); <br>    DECLARE @edate CHAR(10);   </span></span>   
<span> 4</span>  <span>SET</span> <span>@sdate</span> <span>=</span> <span>'</span><span>2015-08-01</span><span>'</span><span>--</span><span>开始日期</span>
<span> 5</span>  <span>SET</span> <span>@edate</span> <span>=</span> <span>'</span><span>2015-12-1</span><span>'</span> 
<span> 6</span>  
<span> 7</span>  <span>/*</span><span>存入临时表</span><span>*/</span>
<span> 8</span>   <span>SELECT</span> <span>*</span> <span>into</span><span> #DateArr 
</span><span> 9</span>   <span>from</span><span> (
</span><span>10</span>      <span>select</span> 
<span>11</span>      <span>CONVERT</span>(<span>varchar</span>(<span>6</span>),<span>DATEADD</span>(<span>MONTH</span>,a.<span>number</span>,<span>@sdate</span>),<span>112</span><span>)             totalDate
</span><span>12</span>      <span>FROM</span> master..spt_values a <span>--</span><span>系统表</span>
<span>13</span>      <span>WHERE</span> a.type <span>=</span> <span>'</span><span>P</span><span>'</span> 
<span>14</span>      <span>AND</span> <span>number</span> <span>BETWEEN</span> <span>0</span> <span>AND</span> (<span>select</span> <span>DATEDIFF</span>(<span>MONTH</span>,<span>@sdate</span>,<span>@edate</span><span>))
</span><span>15</span> <span>)a
</span><span>16</span> 
<span>17</span> <span>select</span> <span>*</span> <span>from</span> #DateArr
登入後複製

 

<span>1</span> <span>/*</span><span>格式化日期序列,用@Months接收</span><span>*/</span>
<span>2</span>       <span>DECLARE</span> <span>@Months</span> <span>VARCHAR</span>(<span>1000</span><span>);
</span><span>3</span>       <span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>);
</span><span>4</span>       
<span>5</span>     <span>SET</span> <span>@SQL</span> <span>=</span> <span>'</span><span>SELECT @Months=STUFF((SELECT DISTINCT </span><span>''</span><span>,[</span><span>''</span><span>+totalDate+</span><span>''</span><span>]</span><span>''</span><span> FROM #DateArr s
</span><span>6</span> <span>    FOR XML PATH(</span><span>''''</span><span>)),1,1,</span><span>''''</span><span>)</span><span>'</span><span>;
</span><span>7</span>     <span>EXECUTE</span> sp_executesql <span>@SQL</span>,N<span>'</span><span>@Months VARCHAR(1000) OUTPUT</span><span>'</span>,<span>@Months</span><span> OUTPUT;
</span><span>8</span> 
<span>9</span>    <span>print</span> <span>@Months</span>
登入後複製

 

<span> 1</span> <span>/*</span><span>未关联时间序列前的基础数据</span><span>*/</span>
<span> 2</span> <span>with</span> tab <span>as</span><span>(
</span><span> 3</span> <span>select</span> 
<span> 4</span> <span>       c.ContractCode 
</span><span> 5</span> <span>       ,c.CustomName
</span><span> 6</span> <span>       ,cl.LineID
</span><span> 7</span>        ,<span>ISNULL</span>(b.TheEndYearAmount,<span>0</span>) <span>as</span><span> NearAYearAgo
</span><span> 8</span> <span>       ,cs.Amount
</span><span> 9</span> <span>       ,cs.Period
</span><span>10</span>         <span>from</span><span> ContractInfo c
</span><span>11</span>     <span>left</span> <span>join</span>
<span>12</span> <span>    ContractLine cl 
</span><span>13</span>     <span>on</span> c.ContractCode<span>=</span><span>cl.ContractCode
</span><span>14</span>     <span>left</span> <span>join</span>
<span>15</span> <span>    ContractSchedule cs
</span><span>16</span>     <span>on</span> cs.LineID<span>=</span><span>cl.LineID
</span><span>17</span>             <span>--</span><span>计算8月份之前的统计</span>
<span>18</span>         <span>left</span> <span>join</span>
<span>19</span> <span>        (
</span><span>20</span>          <span>select</span> LineID,<span>Sum</span>(Amount) <span>as</span><span> TheEndYearAmount
</span><span>21</span>          <span>from</span>  
<span>22</span> <span>         ContractSchedule
</span><span>23</span>          <span>where</span> Period <span>between</span> <span>201508</span> <span>and</span> <span>201512</span>
<span>24</span>          <span>group</span> <span>by</span><span> LineID
</span><span>25</span>           <span>--</span><span>select * from ContractSchedule</span>
<span>26</span>         )b <span>on</span> b.LineID<span>=</span><span>cl.LineID
</span><span>27</span>  ) <span>select</span> <span>*</span> <span>from</span> tab
登入後複製

<span> 1</span> <span>/*</span><span>--------添加日期序列后的统计 --------</span><span>*/</span>
<span> 2</span> <span>SET</span> <span>@SQL</span><span>=</span><span>'</span>
<span> 3</span> <span>with tab as(
</span><span> 4</span> <span>select c.CustomName
</span><span> 5</span> <span>       ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo
</span><span> 6</span> <span>       ,c.ContractCode  --合同号
</span><span> 7</span> <span>       ,cl.LineID  --合同的行ID
</span><span> 8</span> <span>       ,cs.Amount  --待计算的数量
</span><span> 9</span> <span>       ,cs.Period  --统计的日期
</span><span>10</span> <span>        from ContractInfo c
</span><span>11</span> <span>    left join
</span><span>12</span> <span>    ContractLine cl 
</span><span>13</span> <span>    on c.ContractCode=cl.ContractCode
</span><span>14</span> <span>    left join
</span><span>15</span> <span>    ContractSchedule cs
</span><span>16</span> <span>    on cs.LineID=cl.LineID
</span><span>17</span> <span>            --计算8月份之前的统计
</span><span>18</span> <span>        left join
</span><span>19</span> <span>        (
</span><span>20</span> <span>         select LineID,Sum(Amount) as TheEndYearAmount
</span><span>21</span> <span>         from  
</span><span>22</span> <span>         ContractSchedule
</span><span>23</span> <span>         where Period between 201412 and 201508  
</span><span>24</span> <span>         group by LineID
</span><span>25</span> <span>          --select * from ContractSchedule
</span><span>26</span> <span>        )b on b.LineID=cl.LineID
</span><span>27</span> <span> ) select * from tab
</span><span>28</span> <span> PIVOT (SUM(Amount) FOR Period
</span><span>29</span> <span> IN(
</span><span>30</span>    <span>'</span><span>+</span><span>@Months</span><span>+</span><span>'</span>
<span>31</span> <span> ))b
</span><span>32</span>  <span>'</span>
<span>33</span> <span>EXEC</span> (<span>@SQL</span>)
登入後複製

 

查询后结果   脚本下载

在Sqlserver下巧用行列转换日期的数据统计

 

 

 

思考

 

留下的思考

1. 对空值的处理： select * from tab PIVOT (SUM(Amount)...

    这里我尝试用ISNULL（SUM(Amount)，0.00） 去处理，但语法没有通过，我将继续尝试..

2. 脚本片段中获取日期序列，或许在其他统计脚本中也会复用，我准备写到标量函数或表值函数中试一下。

3. 常用的业务统计脚本中关联的表比较多，如何能有效避免重复，在最后结果集中减少使用 distinct ，而使用Group by 去过滤重复字段

这一个知识点我比较薄弱，不断总结，在分享经验给大家，少走弯路。

 

感谢

    我的好朋友欢，一直致力于SQL方面的统计，他给了我很多建议{

1.理解需求并开始写之前，要知道每个表里会出现什么数据

2.出现问题后，先查表与表之间是什么关联，关联从少到多，去检查错误

3.最核心的想清楚再写sql，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了 

  }

   博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。

   永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。

 

本網站聲明
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序，用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具，輕鬆在任何影片中換臉！

顯示更多

熱門文章

<🎜>：種植花園 - 完整的突變指南
3 週前 By DDD
<🎜>：泡泡膠模擬器無窮大 - 如何獲取和使用皇家鑰匙
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
如何修復KB5055612無法在Windows 10中安裝？
3 週前 By DDD
北端：融合系統，解釋
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
Mandragora：巫婆樹的耳語 - 如何解鎖抓鉤
3 週前 By 尊渡假赌尊渡假赌尊渡假赌
顯示更多

熱工具

記事本++7.3.1

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

SublimeText3漢化版

中文版，非常好用

禪工作室 13.0.1

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

顯示更多

熱門話題

Java教學
1666
14
CakePHP 教程
1425
52
Laravel 教程
1324
25
PHP教程
1272
29
C# 教程
1251
24
顯示更多
Related knowledge
sqlserver怎麼匯入mdf文件 sqlserver怎麼匯入mdf文件 Apr 08, 2024 am 11:41 AM

匯入步驟如下：將 MDF 檔案複製到 SQL Server 的資料目錄（通常為 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，開啟資料庫並選擇「附加」。點選“新增”按鈕，選擇 MDF 檔案。確認資料庫名稱，點選確定按鈕即可。

sqlserver資料庫中已存在名為的物件怎麼解決 sqlserver資料庫中已存在名為的物件怎麼解決 Apr 05, 2024 pm 09:42 PM

對於 SQL Server 資料庫中已存在同名對象，需要採取下列步驟：確認物件類型（表格、檢視、預存程序）。如果物件為空，可使用 IF NOT EXISTS 跳過建立。如果物件有數據，使用不同名稱或修改結構。使用 DROP 刪除現有物件（謹慎操作，建議備份）。檢查架構更改，確保沒有引用刪除或重新命名的物件。

Hibernate 框架中 HQL 和 SQL 的差異是什麼？ Hibernate 框架中 HQL 和 SQL 的差異是什麼？ Apr 17, 2024 pm 02:57 PM

HQL和SQL在Hibernate框架中進行比較：HQL（1.物件導向語法，2.資料庫無關的查詢，3.類型安全），而SQL直接操作資料庫（1.與資料庫無關的標準，2.可執行複雜查詢和資料操作）。

怎麼查看sqlserver連接埠號 怎麼查看sqlserver連接埠號 Apr 05, 2024 pm 09:57 PM

若要查看 SQL Server 連接埠號碼：開啟 SSMS，連線到伺服器。在物件資源管理器中找到伺服器名稱，右鍵單擊它，然後選擇“屬性”。在「連線」標籤中，查看「TCP 連接埠」欄位。

sqlserver誤刪資料庫怎麼恢復 sqlserver誤刪資料庫怎麼恢復 Apr 05, 2024 pm 10:39 PM

若誤刪 SQL Server 資料庫，可採取下列步驟還原：停止資料庫活動；備份日誌檔案；檢查資料庫日誌；復原選項：從備份還原；從交易日誌還原；使用 DBCC CHECKDB；使用第三方工具。請定期備份資料庫並啟用交易日誌以防止資料遺失。

sqlserver服務無法啟動怎麼辦 sqlserver服務無法啟動怎麼辦 Apr 05, 2024 pm 10:00 PM

當 SQL Server 服務無法啟動時，可採取下列步驟解決：檢查錯誤日誌以確定根本原因。確保服務帳戶具有啟動服務的權限。檢查依賴項服務是否正在執行。禁用防毒軟體。修復 SQL Server 安裝。如果修復不起作用，重新安裝 SQL Server。

sqlserver安裝失敗怎麼樣刪除乾淨 sqlserver安裝失敗怎麼樣刪除乾淨 Apr 05, 2024 pm 11:27 PM

如果 SQL Server 安裝失敗，可透過下列步驟清理：解除安裝 SQL Server刪除註冊表項刪除檔案和資料夾重新啟動計算機

sqlserver刪除不乾淨無法重新安裝怎麼辦 sqlserver刪除不乾淨無法重新安裝怎麼辦 Apr 05, 2024 pm 11:30 PM

SQL Server 刪除不乾淨導致無法重新安裝的問題可以透過以下步驟解決：手動刪除檔案和登錄項目；使用SQL Server 安裝卸載工具；使用第三方卸載工具；檢查Windows 事件檢視器；重新啟動電腦；重新安裝SQL Server。

See all articles