目錄概要代碼和實現代碼和實現基本思路代碼第1輪查詢第2輪查詢第3輪查詢第4輪查詢代碼改進附錄概要

我們經常在SQL Server中使用group by語句配合聚合函數，對已有的數據進行分組統計。本文主要介紹一種分組的逆向操作，通過一個遞歸公式，實現ungroup操作。

代碼和實現

我們看一個例子，輸入數據如下，我們有一張產品表，該表顯示了產品的數量。

要求實現Ungroup操作，最后輸出數據如下：

代碼和實現基本思路

要想實現ungroup，顯然需要表格的自連接。自連接的次數取決于total_count的數量。

代碼

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

第1輪查詢

獲取全部total_count 大于0的數據，即全表數據。

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

輸出結果：

第2輪查詢

第2輪子查詢，以第1輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉total_count小于0的產品。

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

輸出結果是：

和第1輪相比較，輸出結果中沒了Flashlight了，因為它的total_count減1后為0，被過濾了。

第3輪查詢

第3輪子查詢，以第2輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉total_count小于0的產品。

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

輸出結果如下：

第4輪查詢

第4輪子查詢，以第3輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉total_count小于0的產品。

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

輸出結果：

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

代碼改進

顯然上述代碼過于冗長，如果產品數量很多，那子查詢的代碼也將大幅度增加。

事實上，從第2輪到第4輪的子查詢，代碼是非常相近的，對于這種情況，無論任何開發語言，我們都可以采用遞歸的方式進行優化處理。對于此類為題，遞歸公式如下：

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

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

對于遞歸終止條件，默認是如果沒有新的記錄參加遞歸，則遞歸終止。本例是按照業務邏輯，設置的終止條件，即total_count需要大于0，這樣也可以做到過濾到最后，不會再有新的記錄參與到遞歸中。

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

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

當我們使用CTE時候，發現每次查詢的代碼類似，就可以考慮采用上述遞歸公式對代碼進行優化。找到初始查詢結果，在相似的代碼中找到遞歸部分以及遞歸終止條件。

附錄

建表和數據初始化代碼

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)

到此這篇關于SQLServer實現Ungroup操作的示例代碼的文章就介紹到這了,更多相關SQLServer Ungroup操作內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！