在日常的开发中尽量少采用拼接语句，但针对多条件联合查询，并有多字段可以偏序的情况下，的确采用拼接语句要方便简单得多，单数据库会因为传入的参数不同而产生不同的计划数，计划数多了，对数据库影响很大。
为了降低计划数，我们采用以下方法
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