目錄概要代碼和實(shí)現(xiàn)代碼和實(shí)現(xiàn)基本思路代碼第1輪查詢第2輪查詢第3輪查詢第4輪查詢代碼改進(jìn)附錄概要

我們經(jīng)常在SQL Server中使用group by語(yǔ)句配合聚合函數(shù)，對(duì)已有的數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)。本文主要介紹一種分組的逆向操作，通過(guò)一個(gè)遞歸公式，實(shí)現(xiàn)ungroup操作。

代碼和實(shí)現(xiàn)

我們看一個(gè)例子，輸入數(shù)據(jù)如下，我們有一張產(chǎn)品表，該表顯示了產(chǎn)品的數(shù)量。

要求實(shí)現(xiàn)Ungroup操作，最后輸出數(shù)據(jù)如下：

代碼和實(shí)現(xiàn)基本思路

要想實(shí)現(xiàn)ungroup，顯然需要表格的自連接。自連接的次數(shù)取決于total_count的數(shù)量。

代碼

自連接操作過(guò)程中涉及大量的子查詢，為了便于代碼后期維護(hù)，我們采用CTE。每次子查詢，total_count自動(dòng)減一，total_count小于0時(shí)，直接過(guò)濾掉，該數(shù)據(jù)不再參與查詢。

第1輪查詢

獲取全部total_count 大于0的數(shù)據(jù)，即全表數(shù)據(jù)。

with cte1 as (select * from products where total_count > 0),

輸出結(jié)果：

第2輪查詢

第2輪子查詢，以第1輪的輸出作為輸入，進(jìn)行表格自連接，total_count減1，過(guò)濾掉total_count小于0的產(chǎn)品。

with cte1 as (select * from products where total_count > 0),cte2 as (select * from (select cte1.id, cte1.name, (cte1.total_count -1) as total_count from cte1join products p1 on cte1.id = p1.id) t where t.total_count > 0)select * from cte2

輸出結(jié)果是：

和第1輪相比較，輸出結(jié)果中沒(méi)了Flashlight了，因?yàn)樗膖otal_count減1后為0，被過(guò)濾了。

第3輪查詢

第3輪子查詢，以第2輪的輸出作為輸入，進(jìn)行表格自連接，total_count減1，過(guò)濾掉total_count小于0的產(chǎn)品。

with cte1 as (select * from products where total_count > 0),cte2 as (select * from (select cte1.id, cte1.name, (cte1.total_count -1) as total_count from cte1join products p1 on cte1.id = p1.id) t where t.total_count > 0),cte3 as (select * from (select cte2.id, cte2.name, (cte2.total_count -1) as total_count from cte2join products p1 on cte2.id = p1.id) t where t.total_count > 0)select * from cte3

輸出結(jié)果如下：

第4輪查詢

第4輪子查詢，以第3輪的輸出作為輸入，進(jìn)行表格自連接，total_count減1，過(guò)濾掉total_count小于0的產(chǎn)品。

with cte1 as (select * from products where total_count > 0),cte2 as (select * from (select cte1.id, cte1.name, (cte1.total_count -1) as total_count from cte1join products p1 on cte1.id = p1.id) t where t.total_count > 0),cte3 as (select * from (select cte2.id, cte2.name, (cte2.total_count -1) as total_count from cte2join products p1 on cte2.id = p1.id) t where t.total_count > 0),cte4 as (select * from (select cte3.id, cte3.name, (cte3.total_count -1) as total_count from cte3join products p1 on cte3.id = p1.id) t where t.total_count > 0)select * from cte4

輸出結(jié)果：

下一次迭代，compass的total_count也將等于0，被過(guò)濾掉，查詢結(jié)果不會(huì)再有新的記錄，所以查詢結(jié)束。我們將cte1，cte2，cte3 和 cte4 合并，合并結(jié)果即是所求。

代碼改進(jìn)

顯然上述代碼過(guò)于冗長(zhǎng)，如果產(chǎn)品數(shù)量很多，那子查詢的代碼也將大幅度增加。

事實(shí)上，從第2輪到第4輪的子查詢，代碼是非常相近的，對(duì)于這種情況，無(wú)論任何開發(fā)語(yǔ)言，我們都可以采用遞歸的方式進(jìn)行優(yōu)化處理。對(duì)于此類為題，遞歸公式如下：

with CTE as (initial query -- 初始查詢union all -- 查詢結(jié)果合并recursive query -- 遞歸部分，即在查詢中直接引用CTERecursive terminatation condition -- 遞歸終止條件)

初始查詢，就是我們的第1輪迭代。遞歸部分，就是我們所謂的相似代碼部分。

對(duì)于遞歸終止條件，默認(rèn)是如果沒(méi)有新的記錄參加遞歸，則遞歸終止。本例是按照業(yè)務(wù)邏輯，設(shè)置的終止條件，即total_count需要大于0，這樣也可以做到過(guò)濾到最后，不會(huì)再有新的記錄參與到遞歸中。

按照上述供述，得到的查詢代碼如下：

with cte as (select * from products where total_count > 0union allselect * from (select cte.id, cte.name, (cte.total_count -1) as total_count from ctejoin products p1 on cte.id = p1.id) t where t.total_count > 0)select id, name from cteorder by id, name

當(dāng)我們使用CTE時(shí)候，發(fā)現(xiàn)每次查詢的代碼類似，就可以考慮采用上述遞歸公式對(duì)代碼進(jìn)行優(yōu)化。找到初始查詢結(jié)果，在相似的代碼中找到遞歸部分以及遞歸終止條件。

附錄

建表和數(shù)據(jù)初始化代碼

if OBJECT_ID('products', 'U') is not null drop table productscreate table products (id int primary key identity(1,1),name nvarchar(50) not null,total_count int not null)insert into products (name, total_count) values ('Water Bottle', 3),('Tent', 2),('Flashlight', 1),('compass',4)

到此這篇關(guān)于SQLServer實(shí)現(xiàn)Ungroup操作的示例代碼的文章就介紹到這了,更多相關(guān)SQLServer Ungroup操作內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！