CREATE Procedure [dbo].[P_FPosts_MyPostsOutput] @UserID varchar(10) As select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid inner join ForumModules c on a.ModuleID=c.ModuleID left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid 看下,这样可以吗?
CREATE Procedure [dbo].[P_FPosts_MyPostsOutput] @UserID varchar(10) As select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid inner join ForumModules c on a.ModuleID=c.ModuleID left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid 看下,这样可以吗?
忘记带条件了,应该是这样 CREATE Procedure [dbo].[P_FPosts_MyPostsOutput] @UserID varchar(10) As select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid and a.userid=@UserID inner join ForumModules c on a.ModuleID=c.ModuleID left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid
As declare @TableMyPosts Table ( PostID varchar(10), Title nvarchar(100), [Name] nvarchar(30), PubTime varchar(19), UserName nvarchar(20) ) ; DECLARE @Max_ReplyPubTime_ReplyID varchar(10) --最后的回复的ID DECLARE @MyPostID varchar(10)--帖子ID DECLARE @ModuleID varchar(10)--帖子对应的模块ID --声明游标 DECLARE MyPostsCursor CURSOR For (Select ForumPosts.PostID ,ForumPosts.ModuleID from ForumPosts where ForumPosts.UserID=@UserID ) -- 获取某用户的所有帖子的PostID
Open MyPostsCursor FETCH MyPostsCursor INTO @MyPostID,@ModuleID WHILE (@@FETCH_STATUS = 0) BEGIN; --最后的回复的ID Set @Max_ReplyPubTime_ReplyID= (select top 1 ForumReplies.ReplyID from ForumReplies where ForumReplies.PostID=@MyPostID order by Pubtime desc) --某个PostID的相关记录(标题,模块名,最后回复时间,最后回复者) If @Max_ReplyPubTime_ReplyID!='' --有回复的情况 Begin insert into @TableMyPosts select ForumPosts.PostID,ForumPosts.Title ,ForumModules.[Name],ForumReplies.PubTime,ForumUsersInfo.UserName from ForumModules , ForumUsersInfo,ForumReplies ,ForumPosts where ForumModules.ModuleID=@ModuleID and ForumReplies.ReplyID= @Max_ReplyPubTime_ReplyID and ForumPosts.PostID=ForumReplies.PostID and ForumPosts.PostID=@MyPostID End Else--无回复的情况 Begin insert into @TableMyPosts Select ForumPosts.PostID,ForumPosts.Title ,ForumModules.[Name],'' as PubTime,'' as UserName From ForumModules ,ForumPosts Where ForumModules.ModuleID=@ModuleID and ForumPosts.PostID=@MyPostID End
FETCH NEXT FROM MyPostsCursor INTO @MyPostID,@ModuleID; END; CLOSE MyPostsCursor;--关闭 DEALLOCATE MyPostsCursor;--销毁
@UserID varchar(10)
As
select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid
inner join ForumModules c on a.ModuleID=c.ModuleID
left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid
看下,这样可以吗?
CREATE Procedure [dbo].[P_FPosts_MyPostsOutput]
@UserID varchar(10)
As
select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid
inner join ForumModules c on a.ModuleID=c.ModuleID
left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid
看下,这样可以吗?
CREATE Procedure [dbo].[P_FPosts_MyPostsOutput]
@UserID varchar(10)
As
select a.*,c.*,d.* From ForumPosts a inner join ForumUsersInfo on a.UserID=b.userid and a.userid=@UserID
inner join ForumModules c on a.ModuleID=c.ModuleID
left join (select userid,count(*) as count from ForumReplies group by UserID,PostID )d on a.userid=d.userid
非常感谢你的思路。--我的主题
CREATE Procedure [dbo].[P_FPosts_MyPostsOutput]
@UserID varchar(10)
As
declare @TableMyPosts Table
(
PostID varchar(10),
Title nvarchar(100),
[Name] nvarchar(30),
PubTime varchar(19),
UserName nvarchar(20)
) ; DECLARE @Max_ReplyPubTime_ReplyID varchar(10) --最后的回复的ID
DECLARE @MyPostID varchar(10)--帖子ID
DECLARE @ModuleID varchar(10)--帖子对应的模块ID
--声明游标
DECLARE MyPostsCursor CURSOR For (Select ForumPosts.PostID ,ForumPosts.ModuleID from ForumPosts where ForumPosts.UserID=@UserID )
-- 获取某用户的所有帖子的PostID
Open MyPostsCursor
FETCH MyPostsCursor INTO @MyPostID,@ModuleID
WHILE (@@FETCH_STATUS = 0)
BEGIN; --最后的回复的ID
Set @Max_ReplyPubTime_ReplyID= (select top 1 ForumReplies.ReplyID from ForumReplies where ForumReplies.PostID=@MyPostID order by Pubtime desc)
--某个PostID的相关记录(标题,模块名,最后回复时间,最后回复者) If @Max_ReplyPubTime_ReplyID!='' --有回复的情况
Begin
insert into @TableMyPosts
select ForumPosts.PostID,ForumPosts.Title ,ForumModules.[Name],ForumReplies.PubTime,ForumUsersInfo.UserName
from ForumModules , ForumUsersInfo,ForumReplies ,ForumPosts
where ForumModules.ModuleID=@ModuleID and
ForumReplies.ReplyID= @Max_ReplyPubTime_ReplyID and ForumPosts.PostID=ForumReplies.PostID
and ForumPosts.PostID=@MyPostID End
Else--无回复的情况
Begin
insert into @TableMyPosts
Select ForumPosts.PostID,ForumPosts.Title ,ForumModules.[Name],'' as PubTime,'' as UserName
From ForumModules ,ForumPosts
Where ForumModules.ModuleID=@ModuleID
and ForumPosts.PostID=@MyPostID End
FETCH NEXT FROM MyPostsCursor INTO @MyPostID,@ModuleID;
END; CLOSE MyPostsCursor;--关闭
DEALLOCATE MyPostsCursor;--销毁
select * from @TableMyPosts--输出
现在用表变量解决了这个问题。