在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，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了
}
博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。
永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。
Terangkan peranan log redo innoDB dan membatalkan log.Apr 15, 2025 am 12:16 AMInnoDB menggunakan redolog dan undologs untuk memastikan konsistensi dan kebolehpercayaan data. 1. Pengubahsuaian halaman data rekod untuk memastikan pemulihan kemalangan dan kegigihan transaksi. 2.UNDOLOGS merekodkan nilai data asal dan menyokong penggantian transaksi dan MVCC.
Apakah metrik utama untuk dicari dalam output yang dijelaskan (jenis, kunci, baris, tambahan)?Apr 15, 2025 am 12:15 AMMetrik utama untuk menjelaskan arahan termasuk jenis, kunci, baris, dan tambahan. 1) Jenis mencerminkan jenis akses pertanyaan. Semakin tinggi nilai, semakin tinggi kecekapan, seperti const adalah lebih baik daripada semua. 2) Kunci memaparkan indeks yang digunakan, dan null menunjukkan tiada indeks. 3) Baris menganggarkan bilangan baris yang diimbas, yang mempengaruhi prestasi pertanyaan. 4) Tambahan memberikan maklumat tambahan, seperti menggunakanFilesort meminta bahawa ia perlu dioptimumkan.
Apakah status sementara dalam menjelaskan dan bagaimana untuk mengelakkannya?Apr 15, 2025 am 12:14 AMMenggunakanTemary menunjukkan bahawa keperluan untuk membuat jadual sementara dalam pertanyaan MySQL, yang biasanya dijumpai di Orderby menggunakan lajur yang berbeza, GroupBy, atau tidak diindeks. Anda boleh mengelakkan berlakunya indeks dan menulis semula pertanyaan dan meningkatkan prestasi pertanyaan. Khususnya, apabila menggunakan pembelian muncul dalam menjelaskan output, ini bermakna MySQL perlu membuat jadual sementara untuk mengendalikan pertanyaan. Ini biasanya berlaku apabila: 1) deduplikasi atau pengelompokan apabila menggunakan yang berbeza atau kumpulan; 2) Susun apabila Orderby mengandungi lajur bukan indeks; 3) Gunakan subquery kompleks atau menyertai operasi. Kaedah Pengoptimuman termasuk: 1) Orderby dan GroupB
Huraikan tahap pengasingan urus niaga SQL yang berbeza (baca yang tidak komited, baca bacaan yang komited, berulang, bersiri) dan implikasinya dalam MySQL/InnoDB.Apr 15, 2025 am 12:11 AMMySQL/InnoDB menyokong empat tahap pengasingan transaksi: ReadUncommitted, ReadCommitted, RepeatableRead dan Serializable. 1. ReadoMuncommitted membolehkan membaca data yang tidak komited, yang boleh menyebabkan bacaan kotor. 2. 3.RepeatableRead adalah tahap lalai, mengelakkan bacaan kotor dan bacaan yang tidak boleh diulang, tetapi bacaan hantu mungkin berlaku. 4. Serializable mengelakkan semua masalah konkurensi tetapi mengurangkan kesesuaian. Memilih tahap pengasingan yang sesuai memerlukan keseimbangan data konsistensi dan keperluan prestasi.
MySQL vs Pangkalan Data Lain: Membandingkan PilihanApr 15, 2025 am 12:08 AMMySQL sesuai untuk aplikasi web dan sistem pengurusan kandungan dan popular untuk sumber terbuka, prestasi tinggi dan kemudahan penggunaan. 1) Berbanding dengan PostgreSQL, MySQL melakukan lebih baik dalam pertanyaan mudah dan operasi membaca serentak yang tinggi. 2) Berbanding dengan Oracle, MySQL lebih popular di kalangan perusahaan kecil dan sederhana kerana sumber terbuka dan kos rendah. 3) Berbanding dengan Microsoft SQL Server, MySQL lebih sesuai untuk aplikasi silang platform. 4) Tidak seperti MongoDB, MySQL lebih sesuai untuk data berstruktur dan pemprosesan transaksi.
Bagaimanakah kardinaliti indeks MySQL mempengaruhi prestasi pertanyaan?Apr 14, 2025 am 12:18 AMCardinality Indeks MySQL mempunyai kesan yang signifikan terhadap prestasi pertanyaan: 1. Indeks kardinaliti yang tinggi dapat lebih berkesan menyempitkan julat data dan meningkatkan kecekapan pertanyaan; 2. Indeks kardinaliti yang rendah boleh membawa kepada pengimbasan jadual penuh dan mengurangkan prestasi pertanyaan; 3. Dalam indeks bersama, urutan kardinaliti yang tinggi harus diletakkan di depan untuk mengoptimumkan pertanyaan.
MySQL: Sumber dan Tutorial untuk Pengguna BaruApr 14, 2025 am 12:16 AMLaluan pembelajaran MySQL termasuk pengetahuan asas, konsep teras, contoh penggunaan, dan teknik pengoptimuman. 1) Memahami konsep asas seperti jadual, baris, lajur, dan pertanyaan SQL. 2) Ketahui definisi, prinsip kerja dan kelebihan MySQL. 3) menguasai operasi CRUD asas dan penggunaan lanjutan, seperti indeks dan prosedur yang disimpan. 4) Biasa dengan debugging kesilapan biasa dan cadangan pengoptimuman prestasi, seperti penggunaan rasional indeks dan pertanyaan pengoptimuman. Melalui langkah -langkah ini, anda akan memahami sepenuhnya penggunaan dan pengoptimuman MySQL.
Mysql dunia nyata: Contoh dan kes penggunaanApr 14, 2025 am 12:15 AMAplikasi dunia nyata MySQL termasuk reka bentuk pangkalan data asas dan pengoptimuman pertanyaan kompleks. 1) Penggunaan Asas: Digunakan untuk menyimpan dan mengurus data pengguna, seperti memasukkan, menanyakan, mengemas kini dan memadam maklumat pengguna. 2) Penggunaan lanjutan: Mengendalikan logik perniagaan yang kompleks, seperti perintah dan pengurusan inventori platform e-dagang. 3) Pengoptimuman Prestasi: Meningkatkan prestasi dengan menggunakan indeks, jadual partisi dan cache pertanyaan.
Alat AI Hot
Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik
AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.
Undress AI Tool
Gambar buka pakaian secara percuma
Clothoff.io
Penyingkiran pakaian AI
AI Hentai Generator
Menjana ai hentai secara percuma.
Artikel Panas
Alat panas
VSCode Windows 64-bit Muat Turun
Editor IDE percuma dan berkuasa yang dilancarkan oleh Microsoft
EditPlus versi Cina retak
Saiz kecil, penyerlahan sintaks, tidak menyokong fungsi gesaan kod
SublimeText3 Linux versi baharu
SublimeText3 Linux versi terkini
Dreamweaver CS6
Alat pembangunan web visual
DVWA
Damn Vulnerable Web App (DVWA) ialah aplikasi web PHP/MySQL yang sangat terdedah. Matlamat utamanya adalah untuk menjadi bantuan bagi profesional keselamatan untuk menguji kemahiran dan alatan mereka dalam persekitaran undang-undang, untuk membantu pembangun web lebih memahami proses mengamankan aplikasi web, dan untuk membantu guru/pelajar mengajar/belajar dalam persekitaran bilik darjah Aplikasi web keselamatan. Matlamat DVWA adalah untuk mempraktikkan beberapa kelemahan web yang paling biasa melalui antara muka yang mudah dan mudah, dengan pelbagai tahap kesukaran. Sila ambil perhatian bahawa perisian ini
