一個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.NameFROM (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 percentName,DepartmentId,ROW_NUMBER() over(partition by name order by departmentid) rowfrom Sample order by DepartmentId) awhere a.row=1order by a.DepartmentId

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

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