select top x with ties和select语句执行顺序

创建一个student表：

create table student(name nvarchar(10) primary key,
                     score int);
go
insert student values('aa',10),
                     ('bb',20),
                     ('cc',30),
                     ('dd',40),
                     ('ee',50),
                     ('ff',60),
                     ('gg',70),
                     ('hh',80),
                     ('ii',90),
                     ('jj',100),
                     ('kk',100),
                     ('ll',90),
                     ('mm',95);

取成绩第一名，使用语句

select top 1 name,score from student order by score desc;

只有一条，可有两个都是100分的呀。

select top 2 name,score from student order by score desc;

可以加上with ties

select top 1 with ties name,score from student order by score desc;

继续看with ties,我取前两名呢？

擦，和取第一名是一样的呀！这不是我要的结果呀，两个100的应该是并列第一名，第二名应该是再往下排呀！

我们继续用with ties，

select top 3 with ties name,score from student order by score desc;

select top 4 with ties name,score from student order by score desc;

经过上面的几个select top x with ties可以看出，他只是根据”top x”中的第x个值去判断是否有重复的，如果第X个值没有重复的，结果和top x无with ties是一样的;当然如果第x个值有重复的，但已经在前一个值显示了，那他的结果和top  x无with ties也是一样的，比如上文中的取前两名，select top 2 with ties name , score from student order by score desc.

top 3 和top 3 with ties结果是一样的。

select top 3 name,score from student order by score desc;

那如果我想让排名的结果是：两个100分的都是第一名，第二名95分，第三名也是两个都是90分

那就要用到窗口函数（也叫开窗函数）

下面这个排名按分数由高向低的排序

select name,score from student order by score desc;

窗口函数也可以的

select row_number() over (order by score desc) as 排名,
          name,
         score
from student;

再看看下面这个语句呢

select dense_rank() over (order by score desc) as 排名,
          name,
          score
from student;

13个人，排名只有11，两个第一，两个第三，这是否就是你想要的呢？

还有个语句，虽然第一是两个人，但是否要把第二名给占掉呢，毕竟是两个人啊

select rank() over (order by score desc) as 排名,
          name,
          score
from student;

第二名和第五名没了，因为第一名是两个，把第二名的名额给占了；第四名又是两个，又把第五名的名额给占了。

 

关于第一个top x with ties的问题，涉及到select语句执行顺序的问题，如果order by是后于select语句执行，那结果还成立吗？个人认为不成立，我一直认为order by是select语句中最后执行的。在网上查询的结果不是很好，有的是select在前执行，有的是order by在前执行。

下面是微软网站上的说明，应该还是select在order by之前，但是top在order by之后，这样就可以理解了。

http://msdn.microsoft.com/zh-cn/library/ms189499.aspx 

SELECT 语句的逻辑处理顺序

以下步骤显示 SELECT 语句的逻辑处理顺序（即绑定顺序）。 此顺序确定在一个步骤中定义的对象何时可用于后续步骤中的子句。 例如，如果查询处理器可以绑定到（访问）在 FROM 子句中定义的表或视图，则这些对象及其列可用于所有后续步骤。 相反，因为 SELECT 子句处于步骤 8，所以，在该子句中定义的任何列别名或派生列都无法被之前的子句引用。 不过，它们可由 ORDER BY 子句之类的后续子句引用。 请注意，该语句的实际物理执行由查询处理器确定，因此顺序可能与此列表不同。

1. 源

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE 或 WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

 

 

另附一图，在网上找的，供参考，来源：http://www.jb51.net/article/32066.htm

where子句为什么不能包含聚合函数，因为求解聚合函数是在where子句之后执行的。