SQL Server分页

--验证分页
--加行号
select row_number()over(order by Id) as RowNum,* from UserInfo;
select row_number() over(order by Id) as RowNum,* from UserInfo;
select row_number() over (order by Id) as RowNum,* from UserInfo;
--分页
select * from 
(select row_number()over(order by Id) as RowNum,* from UserInfo) as t 
where RowNum>=1 and RowNum<=5;

为什么把row_number()...开窗函数包起来成一个子查询,这与select ... from 表名 where...查询语句执行顺序有关

select查询执行顺序:

(1)from 在哪里查

(2)where 查什么样的

(3)select 要显示哪些字段

如果直接在where后加RowNum>=1 and <=5则由于先from UserInfo而UserInfo中没有RowNum这个列,所以会报错, 所以要把row_number()这个查询作为一个子查询来用即作为from一的部分就OK了

 

select row_number() over(order by Id) as rowNum,* from UserInfo;
select * from (select row_number()over(order by Id) as rowNum,* from UserInfo) as t where t.rowNum>=1 and rowNum<=10;