sql优化之临时表的使用

需求：明天4点定时导出execl,展示当天登录人数详情，数据量900左右，涉及表7个

原sql:

with temp(userid,FirstLoginTime,LastLoginTime) 
as( select a.userid, min(a.LastLoginTM) as FirstLoginTime, max(a.LastLoginTM) as LastLoginTime from TLoginRecord a,
TUsers b where a.UserID=b.UserID and b.IsRobot=0 and 
a.LastLoginTM>='2018-01-17 00:00:00' and a.LastLoginTM<='2018-01-17 23:59:59' group by a.userid),

tempA (GameCount,OnlineTime,UserID)as(
select count(*) as GameCount,isnull(sum(GameTime),0)/60 as OnlineTime,UserID from TChangeRecord t1 inner join TChangeRecordUser t2 on 
t1.ID=t2.RecordIndex and EndTime>='2018-01-17 00:00:00' and EndTime<='2018-01-17 23:59:59' group by UserID),

tempB(UserID,TotalPayMoney)as
(select Users_ids as userid,SUM(PayMoney) as TotalPayMoney from Web_RMBCost where PaySuccess=1 
and addtime>='2018-01-17 00:00:00' and addtime<='2018-01-17 23:59:59' group by Users_ids) 
select '2018-02-26' as Reportdate, tp.*,tu.NickName,tf.superid,ISnull(ta.OnlineTime,0) OnlineTime,
Isnull(ta.GameCount,0) GameCount ,ISnull(tb.TotalPayMoney,0) TotalPayMoney,
sy.ShuLiang as ShuLiang from temp tp left join TUsers tu on tp.userid=tu.UserID 
left join TUserInfo tf on tp.userid=tf.UserID 
left join tempA ta on tp.userid=ta.UserID 
left join tempB tb on tp.userid=tb.UserID 
left join Shop_YongHuShangPin sy on tp.userid=sy.Userid and sy.ShangPinId=26

 

优化使用临时表：

select a.* into #temp from( select a.userid, min(a.LastLoginTM) as FirstLoginTime, max(a.LastLoginTM) as LastLoginTime from TLoginRecord a,
TUsers b where a.UserID=b.UserID and b.IsRobot=0 and 
a.LastLoginTM>='2018-01-17 00:00:00' and a.LastLoginTM<='2018-01-17 23:59:59' group by a.userid) a

select b.* into #temp2 from(
select count(*) as GameCount,isnull(sum(GameTime),0)/60 as OnlineTime,UserID from TChangeRecord t1 inner join TChangeRecordUser t2 on 
t1.ID=t2.RecordIndex and EndTime>='2018-01-17 00:00:00' and EndTime<='2018-01-17 23:59:59' group by UserID)b

select c.* into #temp3 from(
select Users_ids as userid,SUM(PayMoney) as TotalPayMoney from Web_RMBCost where PaySuccess=1 
and addtime>='2018-01-17 00:00:00' and addtime<='2018-01-17 23:59:59' group by Users_ids)c

select '2018-02-26' as Reportdate, tp.*,tu.NickName,tf.superid,ISnull(ta.OnlineTime,0) OnlineTime,
Isnull(ta.GameCount,0) GameCount ,ISnull(tb.TotalPayMoney,0) TotalPayMoney,
sy.ShuLiang as ShuLiang from #temp tp left join TUsers tu on tp.userid=tu.UserID 
left join TUserInfo tf on tp.userid=tf.UserID 
left join #temp2 ta on tp.userid=ta.UserID 
left join #temp3 tb on tp.userid=tb.UserID 
left join Shop_YongHuShangPin sy on tp.userid=sy.Userid and sy.ShangPinId=26