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

最新推荐文章于 2025-03-10 15:02:14 发布

weixin_34279184

最新推荐文章于 2025-03-10 15:02:14 发布

阅读量146

点赞数

一段复杂的逻辑,原先的代码我使用#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