ACCESS实现ROWNUMBER方法

admin

1、NOT IN，在数据量比较少的前提下使用。
select * from table_name where id_field not in (select top 3 id_field from table_name)

2、左联法
int page = Convert.ToInt32(Request.Form["page"]);
if (page == 0) page = 1;
int pageSize = Convert.ToInt32(Request.Form["pagesize"]);
if (pageSize == 0) pageSize = 20;
string Sql = @"SELECT TOP {0} A.HD_ID,A.HD_Title FROM HD_Article ORDER BY A.HD_IsFront DESC, A.HD_Date DESC";
if (page > 1)
{
    //ACCESS：左联法,MSSQL：ROWNUMBER()
    Sql = @"SELECT AA.* FROM (" + string.Format(Sql, page * pageSize) + ") AA LEFT JOIN (" + string.Format(Sql, (page - 1) * pageSize) + ") BB ON AA.HD_ID=BB.HD_ID WHERE iif(BB.HD_ID,'0','1')='1'";
}
else
{
    Sql = string.Format(Sql, page * pageSize);
}