首页 > 代码库 > SQL:with ties

SQL:with ties

摘自:

http://www.cnblogs.com/huanghai223/archive/2010/10/26/1861961.html

“从100万条记录中的得到成绩最高的记录”。看到这个题目,通常我们的做法是:

select top 1 * from student order by score desc

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

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就是额外的返回值。

举个例子,假设有如下记录:

studentIDcourseNamescore
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
将返回如下结果:
image 
图1 不带with ties(注意最好一条记录)
select top 6 with ties * from student order by score desc

将返回如下结果:

image

图2  带with ties(多了第7条记录) 

结果一目了然,不用多解释!

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

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

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

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都要,不知道我对那片文章理解错了还是他本来就错了。如果是我理解错了,请告诉我,谢谢!

 

 

 

 

------------>>>