萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> 針對distinct疑問引發的一系列思考

針對distinct疑問引發的一系列思考

有人提出了這樣一個問題,整理出來給大家也參考一下

假設有如下這樣一張表格:

這裡的數據,具有如下的特征:在一個DepartmentId中,可能會有多個Name,反之也是一樣。就是說Name和DepartmentId是多對多的關系。

現在想實現這樣一個查詢:按照DepartmentID排完序之後(第一步),再獲取Name列的不重復值(第二步),而且要保留在第一步後的相對順序。以本例而言,應該返回三個值依次是:ACB

我們首先會想到下面這樣一個寫法

select distinct name from Sample order by DepartmentId

從語義上說,這是很自然的。但是很可惜,這個語句根本無法執行,錯誤消息是:

這個錯誤的意思是,如果使用了DISTINCT(去重復值),則出現在OrderBy後面的字段,必須也出現在SELECT後面,但如果DepartmentID如果也真的出現在SELECT後面,顯然是不會有重復值的,所以結果肯定也是不對的。

select distinct name,DepartmentId from Sample order by DepartmentId


 

那麼,既然DISINCT 與OrderBy結合起來用會有這個的一個問題,我們是否有可能變通一下,例如下面這樣:

SELECT distinct a.Name
FROM (select top 100 percent name from Sample order by DepartmentId) a

想比較之前的寫法,我們用到了子查詢技術。同樣從語義上看,仍熱是很直觀明了的。我想先按照DepartmentId進行排序, 然後再去重復值。但是返回到結果是下面這樣的:

雖然確實去除了重復值,但返回的順序卻是不對的。我們希望是先按照DepartmentId排序之後,然後去除重復值,並且保留排序後的相對順序。

為什麼會出現上面這個結果呢?其實是因為DISTINCT本身是會做排序的,而且這個行為是無法更改的(下圖的執行計劃中可以看到這一點)。所以其實我們之前做的Order by在這裡會失去意義。【實際上,如果觀察ADO.NET Entity Framework等ORM工具中生成的類似的一個查詢,它會自動丟棄Order by的設置】

那麼,這樣的情況下,是不是就不可能實現需求了呢?雖然說,這個需求並不多見,絕大部分時候,DISTINCT作為最後一個操作,做一次排序是合乎情理的。

我是這樣考慮到,既然DISTINCT的這個行為是內置的,那麼是否可以繞過這個操作呢?最終我用的一個解決方案是:我能不能把每個Name都編上一個編號,例如有兩個A的話,第一個A我為它編號為1,第二個編號為2,以此類推。然後,查詢的時候,我先排序,然後篩選那些編號為1的Name,這樣其實也就實現了去重復值了。

SQL Server 2005開始提供了一個ROW_NUMBER的功能,結合這個功能,我實現了下面這樣的查詢:

select a.Name from 
(select top 100 percent
Name,DepartmentId,ROW_NUMBER() over(partition by name order by departmentid) row
from Sample order by DepartmentId) a
where a.row=1
order by a.DepartmentId

然後,我得到了下面這樣的結果,我推敲下來,這應該是符合了之前提到的這個需求的

相比較而言,這個查詢的效率會低一些,這個是可以預見的(可以通過下圖看出一點端倪)。但如果需求是硬性的,那麼犧牲一些性能也是不奇怪的。當然,我們可以再研究看看是否有更優的一些寫法。無論如何,使用內置標准的實現,通常都是相對較快的。

以上就是關於distinct疑問引發的一系列思考,希望對大家的學習有所幫助。

copyright © 萬盛學電腦網 all rights reserved