sqlserver中不能使用limit关键字的解决方案
问题
查询薪资表里第二到第六高的薪资数。
MySQL的解决办法
select salary from Employee order by salary desc limit 1,6
select salary from Employee order by salary desc limit 6 offset 1
-- 上面两行都是表示去第二到第六高的薪资数。
sqlserver的解决办法
sqlserver中不能使用limit关键字。。。
- 用TOP关键字来替代解决下。
select distinct top 6 salary from Employee
where salary not in (
select distinct top 1 salary from Employee order by salary desc
) order by salary desc
- 能用offset fetch
介绍:https://www.yiibai.com/sql/sql-fetch.html
解决:
select salary
from Employee
order by salary desc
OFFSET 1 ROWS
FETCH NEXT 6 ROWS ONLY;
--跳过1行,取接下来6行