在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>)
查询后结果 脚本下载
思考
留下的思考
1. 对空值的处理： select * from tab PIVOT (SUM(Amount)...
这里我尝试用ISNULL（SUM(Amount)，0.00） 去处理，但语法没有通过，我将继续尝试..
2. 脚本片段中获取日期序列，或许在其他统计脚本中也会复用，我准备写到标量函数或表值函数中试一下。
3. 常用的业务统计脚本中关联的表比较多，如何能有效避免重复，在最后结果集中减少使用 distinct ，而使用Group by 去过滤重复字段
这一个知识点我比较薄弱，不断总结，在分享经验给大家，少走弯路。
感谢
我的好朋友欢，一直致力于SQL方面的统计，他给了我很多建议{
1.理解需求并开始写之前，要知道每个表里会出现什么数据
2.出现问题后，先查表与表之间是什么关联，关联从少到多，去检查错误
3.最核心的想清楚再写sql，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了
}
博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。
永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。
MySQLのライセンスは、他のデータベースシステムと比較してどうですか？Apr 25, 2025 am 12:26 AMMySQLはGPLライセンスを使用します。 1）GPLライセンスにより、MySQLの無料使用、変更、分布が可能になりますが、変更された分布はGPLに準拠する必要があります。 2）商業ライセンスは、公的な変更を回避でき、機密性を必要とする商用アプリケーションに適しています。
MyisamよりもInnodbを選びますか？Apr 25, 2025 am 12:22 AMMyisamの代わりにInnoDBを選択する場合の状況には、次のものが含まれます。1）トランザクションサポート、2）高い並行性環境、3）高いデータの一貫性。逆に、Myisamを選択する際の状況には、1）主に操作を読む、2）トランザクションサポートは必要ありません。 INNODBは、eコマースプラットフォームなどの高いデータの一貫性とトランザクション処理を必要とするアプリケーションに適していますが、Myisamはブログシステムなどの読み取り集約型およびトランザクションのないアプリケーションに適しています。
MySQLの外国キーの目的を説明してください。Apr 25, 2025 am 12:17 AMMySQLでは、外部キーの機能は、テーブル間の関係を確立し、データの一貫性と整合性を確保することです。外部キーは、参照整合性チェックとカスケード操作を通じてデータの有効性を維持します。パフォーマンスの最適化に注意し、それらを使用するときに一般的なエラーを避けてください。
MySQLのインデックスのさまざまなタイプは何ですか？Apr 25, 2025 am 12:12 AMMySQLには、B-Treeインデックス、ハッシュインデックス、フルテキストインデックス、空間インデックスの4つのメインインデックスタイプがあります。 1.B-Treeインデックスは、範囲クエリ、ソート、グループ化に適しており、従業員テーブルの名前列の作成に適しています。 2。HASHインデックスは、同等のクエリに適しており、メモリストレージエンジンのHASH_TABLEテーブルのID列の作成に適しています。 3。フルテキストインデックスは、記事テーブルのコンテンツ列の作成に適したテキスト検索に使用されます。 4.空間インデックスは、地理空間クエリに使用され、場所テーブルのGEOM列での作成に適しています。
MySQLでインデックスをどのように作成しますか？Apr 25, 2025 am 12:06 AMtocreateanindexinmysql、usethecreateindexstatement.1）forasinglecolumn、 "createdexidx_lastnameonemployees（lastname）;" 2）foracompositeindexを使用して、 "createindexidx_nameonemployees（lastname、firstname）;" 3）; "3）、" 3）を使用します
MySQLはSQLiteとどのように違いますか？Apr 24, 2025 am 12:12 AMMySQLとSQLiteの主な違いは、設計コンセプトと使用法のシナリオです。1。MySQLは、大規模なアプリケーションとエンタープライズレベルのソリューションに適しており、高性能と高い並行性をサポートしています。 2。SQLiteは、モバイルアプリケーションとデスクトップソフトウェアに適しており、軽量で埋め込みやすいです。
MySQLのインデックスとは何ですか？また、パフォーマンスをどのように改善しますか？Apr 24, 2025 am 12:09 AMMySQLのインデックスは、データの取得をスピードアップするために使用されるデータベーステーブル内の1つ以上の列の順序付けられた構造です。 1）インデックスは、スキャンされたデータの量を減らすことにより、クエリ速度を改善します。 2）B-Tree Indexは、バランスの取れたツリー構造を使用します。これは、範囲クエリとソートに適しています。 3）CreateIndexステートメントを使用して、createIndexidx_customer_idonorders（customer_id）などのインデックスを作成します。 4）Composite Indexesは、createIndexIDX_CUSTOMER_ORDERONORDERS（Customer_Id、Order_date）などのマルチコラムクエリを最適化できます。 5）説明を使用してクエリ計画を分析し、回避します
データの一貫性を確保するために、MySQLでトランザクションを使用する方法を説明します。Apr 24, 2025 am 12:09 AMMySQLでトランザクションを使用すると、データの一貫性が保証されます。 1）StartTransactionを介してトランザクションを開始し、SQL操作を実行して、コミットまたはロールバックで送信します。 2）SavePointを使用してSave Pointを設定して、部分的なロールバックを許可します。 3）パフォーマンスの最適化の提案には、トランザクション時間の短縮、大規模なクエリの回避、分離レベルの使用が合理的に含まれます。
ホットAIツール
Undresser.AI Undress
リアルなヌード写真を作成する AI 搭載アプリ
AI Clothes Remover
写真から衣服を削除するオンライン AI ツール。
Undress AI Tool
脱衣画像を無料で
Clothoff.io
AI衣類リムーバー
Video Face Swap
完全無料の AI 顔交換ツールを使用して、あらゆるビデオの顔を簡単に交換できます。
人気の記事
ホットツール
SublimeText3 Mac版
神レベルのコード編集ソフト（SublimeText3）
SecLists
SecLists は、セキュリティ テスターの究極の相棒です。これは、セキュリティ評価中に頻繁に使用されるさまざまな種類のリストを 1 か所にまとめたものです。 SecLists は、セキュリティ テスターが必要とする可能性のあるすべてのリストを便利に提供することで、セキュリティ テストをより効率的かつ生産的にするのに役立ちます。リストの種類には、ユーザー名、パスワード、URL、ファジング ペイロード、機密データ パターン、Web シェルなどが含まれます。テスターはこのリポジトリを新しいテスト マシンにプルするだけで、必要なあらゆる種類のリストにアクセスできるようになります。
SublimeText3 英語版
推奨: Win バージョン、コードプロンプトをサポート!
AtomエディタMac版ダウンロード
最も人気のあるオープンソースエディター
ゼンドスタジオ 13.0.1
強力な PHP 統合開発環境
ホットトピック
7700
15164014139352128725123029