例如:按总学分降序排列,查询排名第6到第10的学生信息
- 我们先来看一下学分降序排列后的前十名
select top 10 *
from student
order by tot_cred desc
- 查询结果:
常见错误
select top 10 *
from student
order by tot_cred desc
except
select top 5 *
from student
order by tot_cred desc
这样写是会报错的,至少SQL Server 2012不支持
可能的原因:order by之后得到的结果集是有序的,except是集合差运算,而集合内的元素是无序的。所以SQL Server 2012应该认为:关键字except前后都是有序集,应该不是集合。不是集合却用集合差运算,然后就报错了。
方法一
with a as
(select top 10 *
from student
order by tot_cred desc
),b as(
select top 5 *
from student
order by tot_cred desc)
select *
from a
except
select *
from b;
- 将上述错误改正一下即可:用with建两个临时表,然后再取差集,运行结果如下:
方法二
with c as(
select *,row_number() over(order by tot_cred desc ) total from student)
select * from c where total>=6 and total<=10
查询结果:
可能有小伙伴不太懂,我们先来看一下临时表c里面都有什么:
with c as(
select *,row_number() over(order by tot_cred desc ) total from student)
select * from c
查询结果:
由此可见,临时表c的作用是:按总学分降序排列后,给每一行编号,新增列的名字是 total