sqlserver_分页查询_row_number() over()

查询t_user表中301-310记录

IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL DROP TABLE #temp;
BEGIN
select identity(int,1,1) as idd,* into #temp from t_user;
select * from #temp where idd between 301 and 310;
END

SELECT * FROM tempdb.dbo.sysobjects  WHERE name LIKE '%#%'

根据部门分组，显示每个部门的工资等级 
SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank
 FROM employee

---

--方法一：top
select * from t_user where id in(
select top 10 id from(
select top  310 id from t_user order by id asc )TT order by TT.id desc)
order by t_user.id asc 

--方法二：top + not in 
select top 10 * from t_user 
where ID not in(select top (300) ID from t_user order by ID )
 order by ID

--方法三：not exists
select top 10 * from t_user where not exists 
(select 1 from (select top 300 id from t_user order by id)a1 
where a1.id=t_user.id) order by id

--方法四：row_number()
select top 10 * from 
(select row_number()over(order by id)rownumber,* from t_user)a1
 where rownumber>300

select * from 
(select row_number()over(order by id)rownumber,* from t_user) a1 
where rownumber>300 and rownumber<310

select * from 
(select row_number()over(order by id)rownumber,* from t_user)a1 
where rownumber between 301 and 310

select *from 
(select row_number()over(order by id)rownumber,* from 
(select top 310 * from t_user where 1=1 order by id)a)b 
where rownumber>300

[Err] 42S01 - [SQL Server]数据库中已存在名为 ‘#temp’ 的对象

SQLServer数据库分页查询