Mysql Tutorial

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:25 PM

sqlsqlserverStatisticsdateConvert

在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，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了

}

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

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

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

sqlserver数据库中已存在名为的对象怎么解决Apr 05, 2024 pm 09:42 PM

对于 SQL Server 数据库中已存在同名对象，需要采取以下步骤：确认对象类型（表、视图、存储过程）。如果对象为空，可使用 IF NOT EXISTS 跳过创建。如果对象有数据，使用不同名称或修改结构。使用 DROP 删除现有对象（谨慎操作，建议备份）。检查架构更改，确保没有引用删除或重命名的对象。

sqlserver服务无法启动怎么办Apr 05, 2024 pm 10:00 PM

当 SQL Server 服务无法启动时，可采取以下步骤解决：检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用，重新安装 SQL Server。

怎么查看sqlserver端口号Apr 05, 2024 pm 09:57 PM

要查看 SQL Server 端口号：打开 SSMS，连接到服务器。在对象资源管理器中找到服务器名称，右键单击它，然后选择“属性”。在“连接”选项卡中，查看“TCP 端口”字段。

sqlserver数据库在哪里Apr 05, 2024 pm 08:21 PM

SQL Server 数据库文件通常存储在以下默认位置：Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。

Java连接SqlServer错误如何解决May 01, 2023 am 09:22 AM

问题发现这次使用的是SqlServer数据库，之前并没有使用过，但是问题不大，我按照需求文档的步骤连接好SqlServer之后，启动SpringBoot项目，发现了一个报错，如下：刚开始我以为是SqlServer连接问题呢，于是便去查看数据库，发现数据库一切正常，我首先第一时间问了我的同事，他们是否有这样的问题，发现他们并没有，于是我便开始了我最拿手的环节，面向百度编程。开始解决具体报错信息是这样，于是我便开始了百度报错：ERRORc.a.d.p.DruidDataSource$CreateCo

sqlserver英文安装怎么更改中文Apr 05, 2024 pm 10:21 PM

SQL Server 英文安装可通过以下步骤更改为中文：下载相应语言包；停止 SQL Server 服务；安装语言包；更改实例语言；更改用户界面语言；重启应用程序。

Win11无法安装SQL Server的原因及解决方案Dec 27, 2023 pm 07:48 PM

有网友反馈，在win11上无法安装sqlserver这款软件，不知道是怎么回事，根据目前的测试来看，win11存在硬盘问题，部分接口硬盘无法安装这款软件。win11为啥不能安装sqlserver：答：win11不能安装sqlserver是硬盘的问题。1、据了解，win11存在对于硬盘的检测bug。2、这导致sqlserver无法在“三星m.2接口”硬盘上安装。3、因此，如果我们要安装的话，需要准备一块其他硬盘。4、然后将该硬盘安装到电脑里，如果没有额外插槽的话就要换掉之前的硬盘。5、安装完成后，

sqlserver数据库日志怎么查询Apr 05, 2024 pm 09:06 PM

可以通过以下步骤查询 SQL Server 数据库日志：1. 打开 SQL Server Management Studio，连接到数据库服务器；2. 展开“管理”节点，导航到“SQL Server 日志”；3. 选择要查询的日志文件，右键单击并选择“查看日志文件”；4. 浏览日志记录。其他查询日志方法：使用 Transact-SQL 查询、PowerShell Cmdlet。

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Repo: How To Revive Teammates

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

3 weeks agoByDDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

3 weeks agoByDDD

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Hot Topics

Where is the login entrance for gmail email?

7338

1627

1352

1265

1210