需求:明天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