id name age
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
12 twiss 12取出第3行到第5行的记录。
select top 3 * from (select top 5 * from Table_1 ) A order by sysid DESC//异常
结果如下:
12 twiss 12
11 miss 23
10 jack 334select top 3 * from (select top 5 * from Table_1 order by sysid asc) A order by sysid desc//正确
11 miss 23
10 jack 334
9 zhong 65第一条执行语句我单单执行“select top 5 * from Table_1”
结果是
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
如果我再对上面的集合执行“select top 3 * from ”,应该和第二条语句应该结果一样呀。但是为什么会出错呢???
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
12 twiss 12取出第3行到第5行的记录。
select top 3 * from (select top 5 * from Table_1 ) A order by sysid DESC//异常
结果如下:
12 twiss 12
11 miss 23
10 jack 334select top 3 * from (select top 5 * from Table_1 order by sysid asc) A order by sysid desc//正确
11 miss 23
10 jack 334
9 zhong 65第一条执行语句我单单执行“select top 5 * from Table_1”
结果是
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
如果我再对上面的集合执行“select top 3 * from ”,应该和第二条语句应该结果一样呀。但是为什么会出错呢???
insert into @T
select 5,'sunlord',12 union all
select 8,'mr',45 union all
select 9,'zhong',65 union all
select 10,'jack',334 union all
select 11,'miss',23 union all
select 12,'twiss',12select top 5 * from @T
/*
id name age
----------- ------- -----------
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
*/select top 5 * from
(select top 5 * from @T) A order by id desc
/*
id name age
----------- ------- -----------
12 twiss 12
11 miss 23
10 jack 334
9 zhong 65
8 mr 45
*/top没有同级的order by的时候,嵌套就按第二层的order by来排序。
结果是不确定的
没有order by 的top是没有任何意义的
insert into @T
select 5,'sunlord',12 union all
select 8,'mr',45 union all
select 9,'zhong',65 union all
select 10,'jack',334 union all
select 11,'miss',23 union all
select 12,'twiss',12select top 5 * from @T
/*
id name age
----------- ------- -----------
5 sunlord 12
8 mr 45
9 zhong 65
10 jack 334
11 miss 23
*/select * from --这个位置把top去掉,你就容易理解了
(select top 5 * from @T) A order by id desc
/*
id name age
----------- ------- -----------
12 twiss 12
11 miss 23
10 jack 334
9 zhong 65
8 mr 45
*/