本文我们主要介绍了一个SQL Server数据库代码优化的实例，优化前的代码如下：
定义表变量 declare @TenantId INT select @TenantId=100001 DECLARE @temp1 table( aType INT DEFAULT(0), mon int,取今天与入库时间的月份差,如SELECT DATEDIFF(MONTH,'2010-7-1',GETDATE()) = 2 total int ) DECLARE @temp2 table( aType int NOT NULL default 0, mon varchar(40), total int) DECLARE @stat_date DATETIME DEClARE @Index int declare @s varchar(4000), @sql varchar(4000) SET @s = '' SET @Index=1 SET @stat_date = GETDATE()-150 遍历出要统计的月份 WHILE month(@stat_date) <= month(GETDATE()) BEGIN Select @s=@s+ 'M'+convert(varchar(20),@Index)+ ' = max(case when mon = '+QUOTENAME(left(CONVERT(varchar,@stat_date,102),7),'''')+ ' then total else 0 end),' SET @stat_date=DATEADD(MONTH, 1, @stat_date) SET @Index=@Index+1 END Select @s= SUBSTRING(@s,0,len(@s)) print @s 应聘总数 insert into @temp1(aType,mon,total) select 1,DATEDIFF(MONTH,CreateDate,GETDATE()) AS mon,COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId group by DATEDIFF(MONTH,CreateDate,GETDATE()) IF(NOT EXISTS(SELECT 1 FROM @temp1)) BEGIN insert into @temp1(aType,mon,total) SELECT 1,-1,0 END 匹配应聘标识号 Update @temp1 set aType=1 已录用人数 insert into @temp1(aType,mon,total) select 2,DATEDIFF(MONTH,CreateDate,GETDATE()),COUNT(*) total from [REL_PersonJobStoreDB] where TenantId=@TenantId and PhaseId = 4 group by DATEDIFF(MONTH,CreateDate,GETDATE()) IF(NOT EXISTS(SELECT 1 FROM @temp1 WHERE aType=2)) BEGIN insert into @temp1(aType,mon,total) SELECT 2,-1,0 END SELECT * FROM @temp1 匹配应聘标识号 Update @temp2 set aType=2 合并表数据 insert into @temp1 select * from @temp2 select * from @temp1 DECLARE @DATE DATETIME SET @DATE = GETDATE() SELECT GETDATE(),DATEADD(MONTH,-1,GETDATE()),DATEDIFF(MONTH,GETDATE(),DATEADD(MONTH,1,GETDATE())) select aType,mon,avg(total) total from @temp1 group by atype,mon select [aType], M1 = max(case when mon = 6 then total else 0 end), M2 = max(case when mon = 5 then total else 0 end), M3 = max(case when mon = 4 then total else 0 end), M4 = max(case when mon = 3 then total else 0 end), M5 = max(case when mon = 2 then total else 0 end), M6 = max(case when mon = 1 then total else 0 end) from ( select aType,mon,avg(total) total from @temp1 group by atype,mon ) aa group by [aType] print @sql END
优化后的代码如下：
ALTER PROCEDURE [dbo].[Report_ApplyStat] @TenantId int AS BEGIN /* 模板表 */ DECLARE @TEMP TABLE([aType] INT) INSERT INTO @TEMP([aType])VALUES(1),(2); /* 查询条件 */ WITH CONDITION AS( SELECT SC.PersonId, ISNULL(RE.PhaseId,0) AS [PhaseId], DATEDIFF(MONTH,SC.ApplyDate,GETDATE()) AS DIFF FROM SearchCV SC LEFT JOIN [REL_PersonJobStoreDB] RE ON SC.PersonId = RE.PersonId WHERE SC.TenantId = @TenantId), [ApplyCount] AS(SELECT 1 AS [TYPE],DATEDIFF(MONTH,ApplyDate,GETDATE()) AS DIFF,(PersonId) AS [ApplyCount] FROM SearchCV WHERE TenantId = @TenantId), [OfferCount] AS(SELECT 2 AS [TYPE],DIFF,(PersonId) AS [OfferCount] FROM CONDITION WHERE PhaseId = 4 ), [Result] AS( SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [ApplyCount] PIVOT(COUNT([ApplyCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T UNION ALL SELECT [TYPE] AS [aType],[1],[2],[3],[4],[5],[6] FROM [OfferCount] PIVOT(COUNT([OfferCount]) FOR DIFF IN([1],[2],[3],[4],[5],[6]))AS T ) SELECT TP.[aType] AS [aType], ISNULL(RE.[6],0) AS [M1], ISNULL(RE.[5],0) AS [M2], ISNULL(RE.[4],0) AS [M3], ISNULL(RE.[3],0) AS [M4], ISNULL(RE.[2],0) AS [M5], ISNULL(RE.[1],0) AS [M6] FROM @TEMP TP LEFT JOIN [Result] RE ON TP.[aType] = RE.[aType] Order by TP.aType
以上就是SQL Server数据库的代码优化实例的介绍，希望本次的介绍能够对您有所收获！