Top应用选项及SQL执行顺序

写在前面的话

我们常常使用top来查询前几条语句,或使用嵌套的top的方式获取分页的数据,那么我们对top及SQL执行的顺序真正了解吗.

具体如下例.

实例

建表toptest,表结构如下:

列	类型
sid	int
sname	nvarchar(50)

建表语句如下:

create table toptest
(
    sid int,
    sname nvarchar(50)
)

表内数据如下:

sid	sname
1	张三
2	李四
3	王五
4	赵六
5	刘七
6	宋八

插入语句如下:

insert into toptest values (1,'张三')

insert into toptest values (2,'李四')

insert into toptest values (3,'王五')

insert into toptest values (4,'赵六')

insert into toptest values (5,'刘七')

insert into toptest values (6,'宋八')

现在我们想要获取第三条、第四条数据,执行下面的语句:

select top 2 * from (select top 4 * from toptest) t order by sid desc

能够得到我们需要的结果呢?

答案是:不能.

返回的是:

　　6    宋八
　　5    刘七

为什么会出现这样的结果呢?

那么我们来看看执行(select top 4 * from toptest) 返回的是什么结果呢?

结果是:

　　1    张三
　　2    李四
　　3    王五
　　4    赵六

那么为什么从表t中返回的就不是

　　3    王五
　　4    赵六

了呢?

这里我们可以在SQLServer里查看执行计划,执行SQL语句,在执行计划标签中得到下图:

我们再来执行下列语句:

set showplan_all on

然后在执行SQL语句,在结果标签中,得到如下结果:

|--select top 2 sid,sname from (select top 4 sid,sname from toptest ) t order by sid desc;
    |--Top(TOP EXPRESSION:((2)))
         |--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC))
              |--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

此处,我们看到执行步骤如下:

1.表扫描

2.先按照sid进行降序排序,然后再选取top 4,

此处为何先执行排序再进行top操作请看下面的SQL逻辑处理步骤

SQL逻辑处理步骤:

(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table>
(3)    <join_type> JOIN <right_table>
(2)      ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

3.选取top 2 sid,sname

4.获取整个结果集

由此,我们可以看到 order by sid desc 虽然是写在外部的,但是却在子查询时已经被执行了.所以,在第二步时,实际上获取了:

　　6    宋八
　　5    刘七
　　4    赵六
　　3    王五

然后,再执行第四步的top 2,即获取了:

　　6    宋八
　　5    刘七

现在,我们来执行下列语句,看执行计划和执行结果是什么?

select top 2 sid,sname from (select top 4 sid,sname from toptest order by sid asc) t order by sid desc;

注意,此处的子查询中加入了order by sid asc

|--select top 2 sid,sname from (select top 4 sid,sname from toptest order by sid asc) t order by sid desc;
    |--Sort(TOP 2, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC))
         |--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] ASC))
              |--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

至此,我们得到了需要的数据.

由此,我们也想到了公用表表达式(CTE),CTE中如何实现该功能呢?

with t
as
(
    select top 4 sid,sname 
    from toptest 
    order by sid asc
)
select top 2 sid,sname
from t 
order by sid desc

执行计划图:

执行计划:

|--with t as (select top 4 sid,sname from toptest order by sid asc) select top 2 sid,sname from t order by sid desc;
    |--Sort(TOP 2, ORDER BY:([SQLTest].[dbo].[toptest].[sid] DESC))
         |--Sort(TOP 4, ORDER BY:([SQLTest].[dbo].[toptest].[sid] ASC))
              |--Table Scan(OBJECT:([SQLTest].[dbo].[toptest]))

结果:

　　4    赵六
　　3    王五

其实,上述错误的查询中之所以会被认为是正确的,那是因为我们在默认的情况下已经给表中的数据按照sid进行排序了,可实际上:

select top 4 sid,sname 
from toptest 

默认是按照sid来进行排序的吗?

现在我们来执行下列语句:

insert into toptest 
values (0,'玲玲')

update toptest
set sid=1000
where sid=2

此时,数据库中的数据发生了变化,具体如下:

sid	sname
1	张三
1000	李四
3	王五
4	赵六
5	刘七
6	宋八
0	玲玲

此时,我们来执行

select top 4 sid,sname from toptest 

会是什么结果呢?返回的会是:

　　0    玲玲
　　1    张三
　　3    王五
　　4    赵六

吗?

各位同学,如果你看到此处,亲手执行一下,看是何结果呢?

此时,如果再执行

select top 2 * from (select top 4 * from toptest) t order by sid desc

得到的又是什么结果呢?

小结:

实际上,在使用top查询时,如果不使用order by子句,那么其查询结果也是不确定.它返回的是SQLServer物理上最先访问到的行.

如果想要在使用top时得到正确的那么,那么请务必使用order by子句.

鸣谢

追索的来谈谈SQL数据库中"简单的"SELECT TOP—可能有你从未注意到的细节一文使我意识到表面上看起来简单的实际上却并不简单.

CSDN网站的子查询执行的顺序问题一文也有许多同学进行了讨论.