UC知道哪位SQL高手帮我把这存储过程优化一下，性能太差了！有效再给高分！
来源：百度知道 编辑：UC知道 时间：2024/05/15 19:55:17
CREATE PROCEDURE GetCTRByTID
@TID int,
@Type varchar(20),
@day int,
@num int,
@ExcludesTID varchar(50)
AS
IF @TID = 0
BEGIN
DECLARE @SQLSTR NVARCHAR(1000)
set @SQLSTR='select PostID,Title from Content where PostID in(select top '+STR(@num)+'
postid from CTRCount where CommentType='''+@type+''' and ClickTime > dateadd(day,-'+STR(@day)+'
,getdate() '+@ExcludesTID+') group by Postid order by count(Postid) desc)'
END
ELSE
IF @TID !=162
BEGIN
set @SQLSTR='select PostID,Title from Content where PostID in(select top '+STR(@num)+'
postid from CTRCount where TID in( select TID from Tab where (Parent_TID='+STR(@TID)+' or TID=
'+STR(@TID)+')'+@ExcludesTID+') and CommentType=''' + @type+ ''&#
@TID int,
@Type varchar(20),
@day int,
@num int,
@ExcludesTID varchar(50)
AS
IF @TID = 0
BEGIN
DECLARE @SQLSTR NVARCHAR(1000)
set @SQLSTR='select PostID,Title from Content where PostID in(select top '+STR(@num)+'
postid from CTRCount where CommentType='''+@type+''' and ClickTime > dateadd(day,-'+STR(@day)+'
,getdate() '+@ExcludesTID+') group by Postid order by count(Postid) desc)'
END
ELSE
IF @TID !=162
BEGIN
set @SQLSTR='select PostID,Title from Content where PostID in(select top '+STR(@num)+'
postid from CTRCount where TID in( select TID from Tab where (Parent_TID='+STR(@TID)+' or TID=
'+STR(@TID)+')'+@ExcludesTID+') and CommentType=''' + @type+ ''&#
把in后面的语句的记录，建立个临时表来存储 同时将检索用的字段加上索引 速度会有很大提升
in的效率很低，建议用表连接
想要要储存点鼠标右键点复制想添加到那就到哪