本文共 867 字,大约阅读时间需要 2 分钟。
例如:按总学分降序排列,查询排名第6到第10的学生信息
select top 10 *from studentorder by tot_cred desc
select top 10 *from studentorder by tot_cred descexceptselect top 5 *from studentorder by tot_cred desc
这样写是会报错的,至少SQL Server 2012不支持
可能的原因:order by之后得到的结果集是有序的,except是集合差运算,而集合内的元素是无序的。所以SQL Server 2012应该认为:关键字except前后都是有序集,应该不是集合。不是集合却用集合差运算,然后就报错了。
with a as(select top 10 *from studentorder by tot_cred desc),b as(select top 5 *from studentorder by tot_cred desc)select *from a exceptselect *from b;
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