数据库的结构:BigEagle的数据库结构

on 2009-2-12 in DotNet | 0 Comment


*bbs表*/
exists(select * from sysobjects where id = object_id(\'BBS\'))
drop table BBS
go

create table BBS
(
id identity primary key ,
RootID default 0 not null , --根ID
FatherID default 0 not null , --父ID
Layer tiny default 0 not null , --层
OrderNum float(53) default 0 not null , --排序基数
UserID default 0 not null , --发言人ID
ForumID tiny default 1 not null , --版面ID
Subject varchar(255) default \'\' not null , --主题
Content text default \'\' not null , --内容
FaceID tiny default 1 not null , --表情
Hits default 0 not null , --点击数
IP varchar(20) default \'\' not null , --发贴IP
Time datetime default getdate not null , --发表时间
Posted bit default 0 not null --是否精华贴子
)
go


/*forum版面表*/
exists(select * from sysobjects where id = object_id(\'forum\'))
drop table forum
go

create table Forum
(
ID tiny identity primary key ,
RootID tiny default 0 not null , --根ID
FatherID tiny default 0 not null , --父ID
Layer tiny default 0 not null , --层
Title varchar(50) default \'\' not null , --版面名称
Description varchar(255) default \'\' not null , --版面描述
MasterID default 1 not null , --版主ID
TopicCount default 0 not null , --贴子总数
Time datetime default getdate not null , --创建时间
IsOpen bit default 0 not null --是否开放
)
go

/*************************************************************************/
/* */
/* procedure : up_GetTopicList */
/* */
/* Description: 贴子列表 */
/* */
/* Parameters: @a_ForumID : 版面id */
/* @a_PageNo: 页号 */
/* @a_PageSize: 每页显示数以根贴为准 */
/* */
/* Use table: bbs , forum */
/* */
/* Author: [email protected] */
/* */
/* Date: 2000/2/14 */
/* */
/* History: */
/* */
/*************************************************************************/
exists(select * from sysobjects where id = object_id(\'up_GetTopicList\'))
drop proc up_GetTopicList
go

create proc up_GetTopicList
@a_ForumID ,
@a_PageNo ,
@a_PageSize
as
/*定义局部变量*/
declare @BeginID
declare @EndID
declare @RootRecordCount
declare @PageCount
declare @RowCount
/*关闭计数*/
nocount _disibledevent=> (-1)

/*求总共根贴数*/
select @RootRecordCount = count(*) from bbs where fatherid=0 and forumid=@a_ForumID
(@RootRecordCount = 0) --如果没有贴子则返回零
0

/*判断页数是否正确*/
(@a_PageNo - 1) * @a_PageSize > @RootRecordCount
(-1)

/*求开始rootID*/
@RowCount = (@a_PageNo - 1) * @a_PageSize + 1
/*限制条数*/
rowcount @RowCount
select @BeginID = rootid from bbs where fatherid=0 and forumid=@a_ForumID
order by id desc

/*结束rootID*/
@RowCount = @a_PageNo * @a_PageSize
/*限制条数*/
rowcount @RowCount
select @EndID = rootid from bbs where fatherid=0 and forumid=@a_ForumID
order by id desc

/*恢复系统变量*/
rowcount 0
nocount off

select a.id , a.layer , a.forumid , a.subject , a.faceid , a.hits , a.time , a.UserID , a.fatherid , a.rootid ,
\'Bytes\' = datalength(a.content) , b.UserName , b.Email , b.HomePage , b.Signature , b.Po
from bbs as a join BBSUser as b _disibledevent=>where Forumid=@a_ForumID and a.rootid between @EndID and @BeginID
order by a.rootid desc , a.ordernum desc
(@@rowcount)
--select @@rowcount
go

Tags:  数据库结构设计 数据库体系结构 数据库结构 数据库的结构

延伸阅读

最新评论

发表评论