SQL With Ties 用法

今天在园子里看了关于索引的一道经典面试题，讲了这样一个问题“从100万条记录中的得到成绩最高的记录”。看到这个题目，通常我们的做法是： select top 1 * from student order by score desc 但是这样做你会发现，如果有几个人分数并列第一，这样就只能取

今天在园子里看了关于索引的一道经典面试题，讲了这样一个问题“从100万条记录中的得到成绩最高的记录”。看到这个题目，通常我们的做法是：

<pre class="brush:php;toolbar:false">select top 1 * from student order by score desc

但是这样做你会发现，如果有几个人分数并列第一，这样就只能取到一个记录。用下面的代码的话，就可以正确地取出分数第一的所有记录：

<pre class="brush:php;toolbar:false">select top 1 with ties * from student order by score desc

由于以前没有用过with ties ，看到这个比较新奇，故随后MSDN，Google，Baidu之。

WITH TIES    

指定从基本结果集中返回额外的行，对于 ORDER BY 列中指定的排序方式参数，这些额外的返回行的该参数值与 TOP n (PERCENT) 行中的最后一行的该参数值相同。只能在 SELECT 语句中且只有在指定了 ORDER BY 子句之后，才能指定 TOP...WITH TIES。

注意：返回的记录关联顺序是任意的。ORDER BY 不影响此规则

来源：MSDN，http://msdn.microsoft.com/zh-cn/library/ms189463.aspx

MSDN中指出这些额外的返回行的参数值与TOP n（PERCENT）行中的最后一行的该参数值相同。这个地方该怎么理解呢？其实是如果按照order by 参数排序TOP n（PERCENT）返回了前面n（pencent）个记录，但是n+1…n+k条记录和排序后的第n条记录的参数值（order by 后面的参数）相同，则n+1、…、n+k也返回。n+1、…、n+k就是额外的返回值。

举个例子，假设有如下记录：

studentID	courseName	score
09212744	数据库	90
09212745	数据库	90
09212746	数据库	90
09212750	数据库	85
09212719	数据库	84
09212720	数据库	80
09212742	数据库	80
09212751	数据库	75
09212755	数据库	74
09212740	数据库	70

select top 6 * from student order by score desc

将返回如下结果：

<img title="image" alt="image" src="http://www.68idc.cn/help/uploads/allimg/151109/102423J92-0.png"    style="max-width:90%"  style="max-width:90%"> 

图1 不带with ties（注意最后一条记录）

<pre class="brush:php;toolbar:false">select top 6 with ties * from student order by score desc

将返回如下结果：

图2  带with ties（多了第7条记录） 

结果一目了然，不用多解释！

插曲：其实在这个过程中有个小插曲，我首先是Google “with ties” 搜到了这篇文章With Ties on SQL Server 2005。内容如下（翻译之后）：

SQL Server 2005有一个功能来选择top记录，但是我要说的是，我希望同样的数量将被加载。举例来说，我想从表中记录选择前5名的货币汇率，但是如果其中一个记录有相同的值，不要让算作前5名。通过下面的例子来证明。

BatNbr CuryRate
000345 9900  
000350 9900  
000351 9900  
000400 9800  
000450 9750

如果你看结果，你会看到9900被加载了3次，让我们看看如果运行下面的脚步。

• select top 5 with ties * from batch where module = ‘CA’ order by CuryRate desc

BatNbr CuryRate
000345 9900  
000350 9900  
000351 9900  
000400 9800  
000450 9750  
000451 9750  
000475 9500  
000456 9400

这就是With Ties on SQL Server 2005的全部内容，这导致我对with ties理解错误！认为加上with ties后，会返回除了重复的记录会返回n条记录，还一直认为是MSDN解释错了（⊙﹏⊙b汗）。而且这篇文章被许多人装载了，博客园和csdn都有，不知道我对那片文章理解错了还是他本来就错了。如果是我理解错了，请告诉我，谢谢！

作者：吴秦
出处：http://www.cnblogs.com/skynet/
本文基于署名 2.5 中国大陆许可协议发布，欢迎转载，演绎或用于商业目的，但是必须保留本文的署名吴秦（包含链接）.

select top 1 with ties * from student order by score desc
select top 6 with ties * from student order by score desc