【www.hzclsc.cn--模拟经营】

Create Table #IDs
(
TempID int IDENTITY (1, 1) NOT NULL,
EntryID int not null
)
Insert into #IDs(EntryID) select DISTINCT [ID] from view_Content where CategoryID=@CategoryID and blogID=@BlogID order by [ID] desc
SELECT vc.*
FROM View_Content vc
INNER JOIN #IDS tmp ON (vc .[ID] = tmp.EntryID)
WHERE tmp.TempID @PageLowerBound
AND tmp.TempID @PageUpperBound and vc.Hiding=0
ORDER BY tmp.TempID
SELECT @Count=COUNT(*) FROM #IDS
SELECT @Count=COUNT(*) FROM #IDS
DROP TABLE #IDS
return @Count
GO

在Access中由于不支持存储过程,不能建立临时表只能在程序中实现
Access中实现如下,这也是我在myblog Access版中使用的:
public List DayBook GetPagedPost(PagedPost p, out int TotalRecords)
{
List DayBook list = new List DayBook

using (OleDbConnection conn = GetOleDbConnection())
{
StringBuilder sql = new StringBuilder();
sql.AppendFormat( select [ID] from blog_Content as p //构造查询条件
if (p.CategoryID 0)
{
sql.AppendFormat( ,blog_Categories AS c, blog_Links AS l WHERE c.CategoryID=l.CategoryID and (p.ID=l.PostID ) and c.CategoryID={1} and p.BlogID={0} ,p.BlogID, p.CategoryID);
}
else
{
sql.AppendFormat( where p.blogID={0} , p.BlogID);
}
if (p.PostType != PostType.Undeclared)
{
sql.AppendFormat( and p.PostType={0} , (int)p.PostType);
}
sql.Append( order by p.[DateUpdated] desc
// NetDiskContext.Current.Context.Response.Write(sql.ToString());
//NetDiskContext.Current.Context.Response.End();
OleDbCommand MyComm = new OleDbCommand(sql.ToString(), conn);
List int IDs = new List int //获取主题ID列表
conn.Open();
using (OleDbDataReader dr = MyComm.ExecuteReader())
{
while (dr.Read())
{
IDs.Add((int)dr[0]);
}
}
TotalRecords=IDs.Count;//返回记录总数
if (TotalRecords 1)
return list;
int pageLowerBound = p.PageSize * p.PageIndex - p.PageSize;//记录索引
int pageUpperBound = pageLowerBound + p.PageSize ;
StringBuilder sb = new StringBuilder();
if (TotalRecords = pageLowerBound)
for (int i = pageLowerBound; i TotalRecords i pageUpperBound; i++)
{
sb.AppendFormat( {0}, , IDs[i]);//构造ID in() 条件,取其中一页
}
else return list; //如没有记录返回空表
if(sb.Length 1)
sb.Remove(sb.Length - 1, 1);//删除最后一个逗号
MyComm.CommandText = string.Format( SELECT b.* , c.Account as Account FROM blog_Content b, Blog_Config c where b.BlogID=c.BlogID and b.[ID] in ({0}) order by b.dateadded desc , sb.ToString());
using (OleDbDataReader dr = MyComm.ExecuteReader())
{
while (dr.Read())
{
list.Add(DataHelp.LoadDayBook(dr));
}
}
return list;
}
}

本文来源：http://www.hzclsc.cn/danjiyouxi/38194.html