在日常的开发中尽量少采用拼接语句，但针对多条件联合查询，并有多字段可以偏序的情况下，的确采用拼接语句要方便简单得多，单数据库会因为传入的参数不同而产生不同的计划数，计划数多了，对数据库影响很大。
为了降低计划数，我们采用以下方法
ALTER PROCEDURE pppp @nPageIndex INT , @nPageSize INT , @vcChatRoomId varchar(200), @vcWXID varchar(200), @vcType varchar(10), @vcDay varchar(10), @nSwith int, @vcKeyword varchar(40), @vcRobotWxId varchar(200), @vcSort varchar(10) AS BEGIN ----优化 DECLARE @vcListSql nVARCHAR(1000)= '' DECLARE @vcListSql1 nVARCHAR(3000)= '' DECLARE @vcListSql2 nVARCHAR(1000)= '' DECLARE @vcSumSql nVARCHAR(4000)= '' DECLARE @vcCommonSql nvarchar(4000)= '' --表 DECLARE @vcSigleSql nvarchar(4000)= '' --个人 DECLARE @vcSortSql VARCHAR(100)= '' DECLARE @vcSigleSortSql VARCHAR(100)= '' declare @temp_date_start varchar(10) declare @temp_date_end varchar(10) DECLARE @date1_1 varchar(50) DECLARE @date1_2 varchar(50) DECLARE @vcPage varchar(4) if @vcKeyword ='' begin set @vcKeyword = '%' end else begin --set @vcKeyword = '%'+RTRIM(@vcKeyword) + '%' set @vcKeyword = '%'+@vcKeyword + '%' end DECLARE @inAdmin VARCHAR(200)='' --排序 if @vcType = 'fy' --发言 begin SET @vcSortSql = ' bb.inMsgCount ' + @vcSort SET @vcSigleSortSql = ' bb.inMsgCount desc' end else if @vcType = 'dt' --动态 begin set @vcSortSql = ' bb.inLinkCount ' + @vcSort set @vcSigleSortSql = ' bb.inLinkCount desc' end else if @vcType = 'qd' --签到 begin set @vcSortSql = ' bb.inQd ' + @vcSort set @vcSigleSortSql = ' bb.inQd desc' END ELSE IF @vcType='gl' --管理员 begin SET @vcSortSql = ' bb.inMsgCount ' + @vcSort SET @vcSigleSortSql = ' bb.inMsgCount desc' SET @inAdmin=' and inAdmin = 2' end ELSE IF @vcType='fhb' begin SET @vcSortSql = ' bb.nmMoney ' + @vcSort SET @vcSigleSortSql = ' bb.nmMoney desc' end ELSE IF @vcType='qhb' begin SET @vcSortSql = ' bb.nmTakeMoney ' + @vcSort SET @vcSigleSortSql = ' bb.nmTakeMoney desc' end set @temp_date_end = CONVERT(VARCHAR(10), GETDATE(),23) if @vcDay = '' begin set @date1_1 ='1900-01-01 00:00:00' set @date1_2 = @temp_date_end +' 23:59:59' end else if @vcDay = 'd' begin set @temp_date_start = CONVERT(VARCHAR(10), GETDATE(),23) set @date1_1 = @temp_date_start +' 00:00:00' set @date1_2 = @temp_date_end +' 23:59:59' end else if @vcDay = 'w' begin set @temp_date_start = CONVERT(VARCHAR(10), GETDATE()-7,23) set @date1_1 = @temp_date_start +' 00:00:00' set @date1_2 = @temp_date_end +' 23:59:59' end else if @vcDay = 'm' begin set @temp_date_start = CONVERT(VARCHAR(10), GETDATE()-31,23) set @date1_1 = @temp_date_start +' 00:00:00' set @date1_2 = @temp_date_end +' 23:59:59' end --拼接Sql语句 --累计条数分页 set @vcPage = CONVERT(VARCHAR(10),(@nPageIndex * @nPageSize)) SET @vcListSql = 'SELECT ROW_NUMBER() OVER (order by OrderByColumn) as nRowNumber,aa.vcWXID,aa.vcHeadImgLocal,aa.dtLastTime,aa.nTheID,aa.vcFatherWX,aa.nRelationType ,aa.vcTag,aa.inAdmin,aa.vcGroupRemarks,aa.vcDisplayName as vcNickName,bb.inMsgCount,bb.inQd,bb.inLinkCount,bb.nmMoney,bb.nmTakeMoney ' set @vcListSql1 = ' FROM TableZZ aa JOIN ( SELECT a.vcChatRoomID,a.vcWXID ,ISNULL(c.inMsgCount,0)as inMsgCount,ISNULL(c.inSignCount,0)as inSignCount ,ISNULL(e.inQd,0) as inQd ,ISNULL(f.inLinkCount,0) as inLinkCount ,isnull(g.nmMoney,0) as nmMoney ,isnull(h.nmTakeMoney,0) as nmTakeMoney FROM TableZZ a left join (select vcChatRoomID,vcWXID,SUM(inMsgCount)inMsgCount,SUM(inSignCount) inSignCount from tabale1 where vcChatRoomID=@vcChatRoomID and dtReportDate >= @date1_1 and dtReportDate<=@date1_2 GROUP BY vcChatRoomID,vcWXID ) c on a.vcChatRoomID=c.vcChatRoomID and a.vcWXID=c.vcWXID left join (select vcChatRoomID,vcWXID,SUM(case when vcWXID is not null then 1 else 0 end) as inQd from table2 where vcChatRoomID=@vcChatRoomID and dtCreateDate >= @date1_1 and dtCreateDate<=@date1_2 GROUP BY vcChatRoomID,vcWXID ) e on a.vcChatRoomID=e.vcChatRoomID and a.vcWXID=e.vcWXID left join (select vcChatRoomID,vcFormWXID,SUM(case when vcFormWXID is not null then 1 else 0 end) as inLinkCount from table3 where vcChatRoomID=@vcChatRoomID and dtCreateTime >= @date1_1 and dtCreateTime<=@date1_2 GROUP BY vcChatRoomID,vcFormWXID ) f on a.vcChatRoomID=f.vcChatRoomID and a.vcWXID=f.vcFormWXID left join (SELECT vcSendWXId,SUM(nmMoney) nmMoney FROM table4 where vcChatRoomID=@vcChatRoomID GROUP BY vcChatRoomID,vcSendWXId ) g ON a.vcWXID=g.vcSendWXId left join (SELECT vcTakeWXID,SUM(nmTakeMoney) nmTakeMoney FROM table5 where vcChatRoomID=@vcChatRoomID GROUP BY vcTakeWXID ) h ON a.vcWXID=h.vcTakeWXID WHERE a.vcChatRoomID=@vcChatRoomID and a.vcWXID <> @vcRobotWxId AND a.inStatus=0 ' set @vcListSql2 = ' and (a.vcNickName like @vcKeyword) ) bb on aa.vcChatRoomID=bb.vcChatRoomID and aa.vcWXID=bb.vcWXID' --group by a.vcChatRoomID,a.vcWXID --记录条数 SET @vcSumSql = 'SELECT 1 AS nResult,COUNT(*) AS nCount ' +@vcListSql1 +@inAdmin + @vcListSql2 -- SET @vcCommonSql = 'SELECT * from (' + @vcListSql +@vcListSql1 +@inAdmin + @vcListSql2 + ' ) as ChatRoomMemberList where nRowNumber BETWEEN 1 AND @vcPage' --个人 SET @vcSigleSql = 'SELECT * from (' + @vcListSql + @vcListSql1 + @vcListSql2 + ' ) as ChatRoomMemberSigle where vcWXID = @vcWXID' --针对排序语句 采用替换 set @vcCommonSql = replace(@vcCommonSql,'OrderByColumn',@vcSortSql); set @vcSigleSql = replace(@vcSigleSql,'OrderByColumn',@vcSigleSortSql); if @nSwith = 0 --是否查询个人 begin -- 针对条件参数，采取以下方式 --显示统计 exec sp_executesql @vcSumSql,N'@date1_1 varchar(50),@date1_2 varchar(50),@vcChatRoomID varchar(100),@vcRobotWxId varchar(200),@vcKeyword varchar(40)',@date1_1,@date1_2,@vcChatRoomID,@vcRobotWxId,@vcKeyword --分页列表 exec sp_executesql @vcCommonSql,N'@date1_1 varchar(50),@date1_2 varchar(50),@vcChatRoomID varchar(100),@vcRobotWxId varchar(200),@vcKeyword varchar(40),@vcPage varchar(4)',@date1_1,@date1_2,@vcChatRoomID,@vcRobotWxId,@vcKeyword,@vcPage --个人信息 exec sp_executesql @vcSigleSql,N'@date1_1 varchar(50),@date1_2 varchar(50),@vcChatRoomID varchar(100),@vcRobotWxId varchar(200),@vcKeyword varchar(40),@vcWXID varchar(200)',@date1_1,@date1_2,@vcChatRoomID,@vcRobotWxId,@vcKeyword,@vcWXID end else begin --print 1 --显示统计 exec sp_executesql @vcSumSql,N'@date1_1 varchar(50),@date1_2 varchar(50),@vcChatRoomID varchar(100),@vcRobotWxId varchar(200),@vcKeyword varchar(40)',@date1_1,@date1_2,@vcChatRoomID,@vcRobotWxId,@vcKeyword --分页列表 exec sp_executesql @vcCommonSql,N'@date1_1 varchar(50),@date1_2 varchar(50),@vcChatRoomID varchar(100),@vcRobotWxId varchar(200),@vcKeyword varchar(40),@vcPage varchar(4)',@date1_1,@date1_2,@vcChatRoomID,@vcRobotWxId,@vcKeyword,@vcPage end end
