sqlserver2008 两种分页操作

1.有唯一项字段（例如id）

SELECT top 分页大小 * FROM 表名 where id not in (select top (分页大小*(第几页-1)) id from 表名 where 搜索字段1='aaa' and 搜索字段2='bbb' order by 排序字段) and 搜索字段1='aaa' and 搜索字段2='bbb order by 排序字段名;
SELECT TOP {page_size} * from {table} WHERE id NOT IN (SELECT TOP {start} id from {table} {('where' + where) if filters else ''} {orders}) {('and' + where) if filters else ''} {orders}

例：

SELECT top 10 * FROM ACstat where id not in (select top 0 id from Future.dbo.ACstat where brokername = '国元期货' order by brokerid,ac) and brokername = '国元期货' order by brokerid,ac;

2.没有唯一项字段

SELECT * FROM (SELECT *,ROW_NUMBER() OVER (order by 排序字段) AS RowNo FROM 表名 where 搜索字段1='aaa' and 搜索字段2='bbb') AS A WHERE RowNo>(分页大小*(第几页-1)) and RowNo<=(分页大小*第几页) where 搜索字段1='aaa' and 搜索字段2='bbb'
SELECT * FROM (SELECT *,ROW_NUMBER() OVER ({orders}) AS RowNo FROM LogRecord.dbo.PythonPosition {('where' + where) if fields else ''}) AS A WHERE RowNo>{start} and RowNo<={end} {('and' + where) if fields else ''}

例：

SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY updatetime desc) AS RowNo FROM dbo.python_position where symbol='a') AS A WHERE RowNo>10 and RowNo<=20 and symbol='a';