快捷導(dǎo)航

SQLServer實現(xiàn)Ungroup操作的示例代碼

更新時間：2023年07月10日 15:33:35 作者：懶人Ethan

本文主要介紹了SQLServer實現(xiàn)Ungroup操作的示例代碼，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

概要

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

代碼和實現(xiàn)

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

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

代碼和實現(xiàn)

基本思路

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

代碼

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

第1輪查詢

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

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

輸出結(jié)果：

第2輪查詢

第2輪子查詢，以第1輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉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 cte1
	join products p1 
	on cte1.id = p1.id) t 
where t.total_count > 0
)
select * from cte2

輸出結(jié)果是：

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

第3輪查詢

第3輪子查詢，以第2輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉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 cte1
	join 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 cte2
	join products p1 
	on cte2.id = p1.id) t 
where t.total_count > 0
)
select * from cte3

輸出結(jié)果如下：

第4輪查詢

第4輪子查詢，以第3輪的輸出作為輸入，進行表格自連接，total_count減1，過濾掉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 cte1
	join 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 cte2
	join 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 cte3
	join products p1 
	on cte3.id = p1.id) t 
where t.total_count > 0
)
select * from cte4

輸出結(jié)果：

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

代碼改進

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

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

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

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

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

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

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

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

附錄

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

if OBJECT_ID('products', 'U') is not null 
drop table products
create 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實現(xiàn)Ungroup操作的示例代碼的文章就介紹到這了,更多相關(guān)SQLServer Ungroup操作內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: