sql 分页，ROW_NUMBER高效率分页(仅支持MSSQL2005及以上)

sql 分页，ROW_NUMBER高效率分页(仅支持MSSQL2005及以上)

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY sort_id asc,add_time desc,id desc) as row_number, * FROM dt_channel_article_news where id>0) AS T WHERE row_number between 1 and 10

后端代码

分页算法

#region 分页算法
int skip = 1;//从第几条开始
int take = PageIndex * PageSize;//到第几条结束
if (PageIndex > 0)
{
skip = ((PageIndex - 1) * PageSize) + 1;
}
#endregion 

拼接SQL分页

string sql=$@"
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY user_id desc) as row_number, 
* FROM userInfo where id>0) 
AS T WHERE row_number between {skip} and {take}";