Discuz 论坛版主回复后在帖子列表后加版主回复字样

最新推荐文章于 2023-07-18 15:10:04 发布

原创最新推荐文章于 2023-07-18 15:10:04 发布 · 2.2k 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个使用SQL存储过程实现的带分页功能的帖子列表查询方法。该存储过程通过参数控制查询指定论坛ID（fid）下的帖子，并能够根据页数、每页显示数量等参数动态调整查询结果。此外，还引入了临时表来标记版主回复的帖子。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

找到存储过程 dnt_gettopiclist

ALTER PROCEDURE [dbo].[dnt_gettopiclist]   
@fid int,
@pagesize int, 
@startnum int, 
@pageindex int 
AS
DECLARE @strSQL varchar(5000)
if object_id('tempdb..#a') is not null drop table #a --创建临时表
select distinct a.tid,'<font color=red>版主回复</font>' tailtitle into #a
 from dnt_posts1 a,dnt_users b where a.posterid=b.uid
and a.fid=@fid and b.groupid<=3--用户组别<=3的

--之后topics 与临时表#a 再做左连接就可以实现了
IF @pageindex = 1
 BEGIN
  SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +' [rate],a.[tid],[iconid],[typeid],[title],b.tailtitle,[price],[hide],[readperm],
[special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[special] FROM 
[dnt_topics] a left join #a b on a.tid=b.tid WHERE [fid]=' +STR(@fid) + ' AND [displayorder]=0  ORDER BY [lastpostid] DESC'
 END
ELSE
 BEGIN
  SET @strSQL = 'SELECT TOP ' + STR(@pagesize) +'[rate], a.[tid],[iconid],[typeid],[title],b.tailtitle,[price],[hide],[readperm],
[special],[poster],[posterid],[replies],[views],[postdatetime],[lastpost],[lastposter],
[lastpostid],[lastposterid],[replies],[highlight],[digest],[displayorder],[attachment],[closed],[magic],[special] FROM 
[dnt_topics] a left join #a b on a.tid=b.tid WHERE [lastpostid] < (SELECT min([lastpostid])  FROM (SELECT TOP ' + STR
((@pageindex-1)*@pagesize-@startnum) + ' [lastpostid] FROM [dnt_topics] WHERE [fid]=' +STR
(@fid) + ' AND [displayorder]=0 ORDER BY [lastpostid] DESC) AS tblTmp ) 
AND [fid]=' +STR(@fid) + ' AND [displayorder]=0 ORDER BY [lastpostid] DESC'
 END
 print @strSQL
EXEC(@strSQL)