針對(duì)distinct疑問引發(fā)的一系列思考

有人提出了這樣一個(gè)問題，整理出來給大家也參考一下

假設(shè)有如下這樣一張表格：

這里的數(shù)據(jù)，具有如下的特征：在一個(gè)DepartmentId中，可能會(huì)有多個(gè)Name，反之也是一樣。就是說Name和DepartmentId是多對(duì)多的關(guān)系。

現(xiàn)在想實(shí)現(xiàn)這樣一個(gè)查詢：按照DepartmentID排完序之后（第一步），再獲取Name列的不重復(fù)值（第二步），而且要保留在第一步后的相對(duì)順序。以本例而言，應(yīng)該返回三個(gè)值依次是：ACB

我們首先會(huì)想到下面這樣一個(gè)寫法

select distinct name from Sample order by DepartmentId

從語義上說，這是很自然的。但是很可惜，這個(gè)語句根本無法執(zhí)行，錯(cuò)誤消息是：

這個(gè)錯(cuò)誤的意思是，如果使用了DISTINCT(去重復(fù)值），則出現(xiàn)在OrderBy后面的字段，必須也出現(xiàn)在SELECT后面，但如果DepartmentID如果也真的出現(xiàn)在SELECT后面，顯然是不會(huì)有重復(fù)值的，所以結(jié)果肯定也是不對(duì)的。

select distinct name,DepartmentId from Sample order by DepartmentId

那么，既然DISINCT 與OrderBy結(jié)合起來用會(huì)有這個(gè)的一個(gè)問題，我們是否有可能變通一下，例如下面這樣：

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

想比較之前的寫法，我們用到了子查詢技術(shù)。同樣從語義上看，仍熱是很直觀明了的。我想先按照DepartmentId進(jìn)行排序, 然后再去重復(fù)值。但是返回到結(jié)果是下面這樣的：

雖然確實(shí)去除了重復(fù)值，但返回的順序卻是不對(duì)的。我們希望是先按照DepartmentId排序之后，然后去除重復(fù)值，并且保留排序后的相對(duì)順序。

為什么會(huì)出現(xiàn)上面這個(gè)結(jié)果呢？其實(shí)是因?yàn)镈ISTINCT本身是會(huì)做排序的，而且這個(gè)行為是無法更改的（下圖的執(zhí)行計(jì)劃中可以看到這一點(diǎn)）。所以其實(shí)我們之前做的Order by在這里會(huì)失去意義?！緦?shí)際上，如果觀察ADO.NET Entity Framework等ORM工具中生成的類似的一個(gè)查詢，它會(huì)自動(dòng)丟棄Order by的設(shè)置】

那么，這樣的情況下，是不是就不可能實(shí)現(xiàn)需求了呢？雖然說，這個(gè)需求并不多見，絕大部分時(shí)候，DISTINCT作為最后一個(gè)操作，做一次排序是合乎情理的。

我是這樣考慮到，既然DISTINCT的這個(gè)行為是內(nèi)置的，那么是否可以繞過這個(gè)操作呢？最終我用的一個(gè)解決方案是：我能不能把每個(gè)Name都編上一個(gè)編號(hào)，例如有兩個(gè)A的話，第一個(gè)A我為它編號(hào)為1，第二個(gè)編號(hào)為2，以此類推。然后，查詢的時(shí)候，我先排序，然后篩選那些編號(hào)為1的Name，這樣其實(shí)也就實(shí)現(xiàn)了去重復(fù)值了。

SQL Server 2005開始提供了一個(gè)ROW_NUMBER的功能，結(jié)合這個(gè)功能，我實(shí)現(xiàn)了下面這樣的查詢：

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

然后，我得到了下面這樣的結(jié)果，我推敲下來，這應(yīng)該是符合了之前提到的這個(gè)需求的

相比較而言，這個(gè)查詢的效率會(huì)低一些，這個(gè)是可以預(yù)見的（可以通過下圖看出一點(diǎn)端倪）。但如果需求是硬性的，那么犧牲一些性能也是不奇怪的。當(dāng)然，我們可以再研究看看是否有更優(yōu)的一些寫法。無論如何，使用內(nèi)置標(biāo)準(zhǔn)的實(shí)現(xiàn)，通常都是相對(duì)較快的。

以上就是關(guān)于distinct疑問引發(fā)的一系列思考，希望對(duì)大家的學(xué)習(xí)有所幫助。