SQL技巧(三) - CTE实战之代替临时表

一段复杂的逻辑,原先的代码我使用#tmp临时表来实现,性能是不好的,而且要考虑到多用户时的锁的问题

代码如下:

declare  @StartDate  datetime
declare  @EndDate  datetime
select  @StartDate = ' 2012-09-28 '
select  @EndDate = ' 2012-10-03 '

if  exists ( select  *  from tempdb..sysobjects  where name  like  ' #tmpPolicyId% ')
     drop  table #tmpPolicyId
if  exists ( select  *  from tempdb..sysobjects  where name  like  ' #tmpSeasonFee% ')
     drop  table #tmpSeasonFee
if  exists ( select  *  from tempdb..sysobjects  where name  like  ' #tmpSpecialFee% ')
     drop  table #tmpSpecialFee
 
select PolicyId
into #tmpPolicyId
from GvInterPolicy
group  by PolicyId

select tmp.PolicyId,  max(AddFee)  as SeasonFee
into #tmpSeasonFee
from #tmpPolicyId tmp  inner  join GvSeasonFee season
     on tmp.PolicyId =season.PolicyId
where season.StartDate <= @StartDate  and season.EndDate >= @StartDate 
group  by tmp.PolicyId

select tmp.PolicyId,  max(AddFee)  as SpecialFee
into #tmpSpecialFee
from #tmpPolicyId tmp  inner  join GvSpecialFee special
     on tmp.PolicyId =special.PolicyId
where special.SpecialDate = @StartDate
group  by tmp.PolicyId


select p.PolicyId, p.AirComCode, p.DepStartTime, p.DepEndTime, p.SellStartTime, p.SellEndTime,
        p.StartCityCode, p.EndCityCode, r.FlightNo, r.SCityCode, r.ECityCode, r.STime, r.ETime,
        r.TerminalBuilding, r.RouteOrder, r.SCityName, r.ECityName, r.PlaneModel, r.IsBack,
        c.FirstCabin, c.SecondCabin, c.Price  as AdultPrice, 
         isnull(season.SeasonFee,  0)  as SeaFee,  isnull(special.SpecialFee,  0)  as SpFee,
        AddPrice = case  when  isnull(season.SeasonFee,  0) >= isnull(special.SpecialFee,  0)  then  isnull(season.SeasonFee,  0)  else  isnull(special.SpecialFee,  0)  end
from GvInterPolicy p  inner  join GvRouteInfo r
     on p.PolicyId =r.PolicyId  inner  join GvCabinInfo c
     on p.PolicyId =c.PolicyId  left  join #tmpSeasonFee season
     on p.PolicyId =season.PolicyId  left  join #tmpSpecialFee special
     on p.PolicyId =special.PolicyId 
where p.DepStartTime <= @StartDate  and p.DepEndTime >= @EndDate
order  by p.PolicyId, c.FirstCabin, c.SecondCabin, r.RouteOrder

 

使用CTE改进后的代码:

declare  @StartDate  datetime
declare  @EndDate  datetime
select  @StartDate = ' 2012-09-28 '
select  @EndDate = ' 2012-10-03 ';

with ctePolicyId(PolicyId)
as
(
     select PolicyId
     from GvInterPolicy
     group  by PolicyId
),
cteSeasonFee
as
(
     select p.PolicyId,  max(AddFee)  as SeasonFee
     from ctePolicyId p  inner  join GvSeasonFee season
         on p.PolicyId =season.PolicyId
     where season.StartDate <= @StartDate  and season.EndDate >= @StartDate 
     group  by p.PolicyId
),
cteSpecialFee
as
(
     select p.PolicyId,  max(AddFee)  as SpecialFee
     from ctePolicyId p  inner  join GvSpecialFee special
         on p.PolicyId =special.PolicyId
     where special.SpecialDate = @StartDate
     group  by p.PolicyId

)

select p.PolicyId, p.AirComCode, p.DepStartTime, p.DepEndTime, p.SellStartTime, p.SellEndTime,
        p.StartCityCode, p.EndCityCode, r.FlightNo, r.SCityCode, r.ECityCode, r.STime, r.ETime,
        r.TerminalBuilding, r.RouteOrder, r.SCityName, r.ECityName, r.PlaneModel, r.IsBack,
        c.FirstCabin, c.SecondCabin, c.Price  as AdultPrice, 
         isnull(season.SeasonFee,  0)  as SeaFee,  isnull(special.SpecialFee,  0)  as SpFee,
        AddPrice = case  when  isnull(season.SeasonFee,  0) >= isnull(special.SpecialFee,  0)  then  isnull(season.SeasonFee,  0)  else  isnull(special.SpecialFee,  0)  end
from GvInterPolicy p  inner  join GvRouteInfo r
     on p.PolicyId =r.PolicyId  inner  join GvCabinInfo c
     on p.PolicyId =c.PolicyId  left  join cteSeasonFee season
     on p.PolicyId =season.PolicyId  left  join cteSpecialFee special
     on p.PolicyId =special.PolicyId 
where p.DepStartTime <= @StartDate  and p.DepEndTime >= @EndDate
order  by p.PolicyId, c.FirstCabin, c.SecondCabin, r.RouteOrder