SQL Server With Ties 用法

官方文档:http://msdn.microsoft.com/en-us/library/ms189463.aspx

TOP用法 (Transact-SQL)

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2012. When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement or affected by an INSERT, UPDATE, MERGE, or DELETE statement.

Syntax语法

[ TOP (expression) [PERCENT]     [ WITH TIES ] ]

Arguments参数

expression

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

PERCENT

Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

WITH TIES

Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BYcolumns in row 5, the result set will contain 7 rows.

TOP…WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. The returned order of tying records is arbitrary. ORDER BY does not affect this rule.

当你想返回两个或两个以上的行系最后一名在有限的结果集。必须使用ORDERBY子句。的关系,可能会导致更多的行返回指定表达式的值比。

例如,如果表达式设置为top 5但第5行包含有3个相等的数据,并列在第5行,那么结果集将包含7行,包括了额外的两行。

Including tie values

Using WITH TIES to include rows that match the values in the last row

The following example obtains the top 10 percent of all employees with the highest salary and returns them in descending order according to their salary. Specifying WITH TIES makes sure that any employees that have salaries equal to the lowest salary returned (the last row) are also included in the result set, even if doing this exceeds 10percent of employees.

Transact-SQL
USE AdventureWorks2012;
GO
SELECT TOP(10)WITH TIES
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

重点,网上的有文章对WITH TIES的分析是错误的,不是返回top n ,前N个结果,而是返回前N-个,第N项如果有多个相同的结果,那就返回这些额外的和第N项相同的结果。

 

参考“从100万条记录中的得到成绩最高的记录”:http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html

select top 1 * from student order by score desc

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

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

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

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

将返回如下结果:

图1 不带with ties(注意最后一条记录)
select top 6 with ties * from student order by score desc

将返回如下结果:

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

带图的比较明了。

About 智足者富

http://chenpeng.info

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>