SQL分页的几种方式

1.使用Row_number() over(order by columnName)函数来作为标示分页(下面的例子都是以last_seen来排序的，要求取顺序为20-30行的数据)

SELECT Username,real_filename,Row_number() over (order by last_seen desc) as rn INTO #tempTable from Allftplog

SELECT * FROM #tempTable where rn between 20 and 30;

2.使用IDENTITY来设定行号，根据行号来分页

SELECT Username,real_filename,IDENTITY(int,1,1) as rn into #tempTable from Allftplog order by last_seen desc;

SELECT * FROM #tempTable where rn between 20 and 30;

3.使用With AS() 来生成临时表

With UNIT AS(SELECT Row_number() over (order by last_seen desc) as rn ,Username,real_filename from Allftplog);

select * from UNIT where rn between 20 and 30

其中，大数据量的时候第三中方式最快，推荐使用。

巴特西