首先创建一张如下的表 test
包含三个字段 id ,birth ,studentId
接下来插入数据:
insert into test values(1,'2012-09-12',213 )
insert into test values(2,'2012-08-12',214 )
insert into test values(3,'2012-10-12',211 )
insert into test values(4,'2012-09-12',256 )
insert into test values(5,'2012-04-12',198 )
insert into test values(6,'2012-04-12',198 )
读者有兴趣,可以去执行以下三条语句,并记录好结果
select * from test
id birth studentID
1 2012-09-12 213
2 2012-08-12 214
3 2012-10-12 211
4 2012-09-12 256
5 2012-04-12 198
6 2012-04-12 198
select top 4 * from test
id birth studentID
1 2012-09-12 213
2 2012-08-12 214
3 2012-10-12 211
4 2012-09-12 256
select * from test order by birth
5 2012-04-12 198
6 2012-04-12 198
2 2012-08-12 214
1 2012-09-12 213
4 2012-09-12 256
3 2012-10-12 211
如果是top先执行,那么结果(studentId)为 213 214 211 256 在去排序
如果是order by先执行,那么结果(studentId)应该是 198 198 214 213
select top 4 * from test
--对于with ties的一个补充。with ties适用于消除select top中有重复的属性(但是算为了两个)的一个关键字
select top 4 * from test order by birth asc
id birth studentID
5 2012-04-12 198
6 2012-04-12 198
2 2012-08-12 214
1 2012-09-12 213
select top 4 with ties * from test order by birth asc
结果
id birth studentID
5 2012-04-12 198
6 2012-04-12 198
2 2012-08-12 214
1 2012-09-12 213
4 2012-09-12 256