建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
SQLServer數(shù)據(jù)庫(kù)的代碼優(yōu)化實(shí)例
本文我們主要介紹了一個(gè)SQL Server數(shù)據(jù)庫(kù)代碼優(yōu)化的實(shí)例，優(yōu)化前的代碼如下：
創(chuàng)新互聯(lián)長(zhǎng)期為上1000家客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為陸豐企業(yè)提供專業(yè)的成都做網(wǎng)站、網(wǎng)站設(shè)計(jì)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)，陸豐網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。
- 定義表變量
- declare @TenantId INT
- select @TenantId=100001
- DECLARE @temp1 table(
- aType INT DEFAULT(0),
- mon int,取今天與入庫(kù)時(shí)間的月份差,如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
- 遍歷出要統(tǒng)計(jì)的月份
- 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
- 應(yīng)聘總數(shù)
- 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
- 匹配應(yīng)聘標(biāo)識(shí)號(hào)
- Update @temp1 set aType=1
- 已錄用人數(shù)
- 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
- 匹配應(yīng)聘標(biāo)識(shí)號(hào)
- Update @temp2 set aType=2
- 合并表數(shù)據(jù)
- 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
優(yōu)化后的代碼如下：
- 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數(shù)據(jù)庫(kù)的代碼優(yōu)化實(shí)例的介紹，希望本次的介紹能夠?qū)δ兴斋@！
【編輯推薦】
- SQL Server 2008 MDX應(yīng)用之組裝有序集合
- SQL Server 2008中的代碼安全之主密鑰詳解
- SQL Server 2008 MDX應(yīng)用之檢索集合中的元組
- SQL Server 2005/2008中的CTE應(yīng)用之遞歸查詢
- 淺析SQL Server數(shù)據(jù)庫(kù)SSIS導(dǎo)入IIS日志的簡(jiǎn)單步驟
分享文章：SQLServer數(shù)據(jù)庫(kù)的代碼優(yōu)化實(shí)例
本文URL：http://www.dlmjj.cn/article/djdigji.html
