首页新闻招聘找找看知识库

sql server sql数据优化

0
[待解决问题]

select tDate.Date,ISNULL(tAdd.addCount,0)addCount,ISNULL(tDown.downCount,0)downCount
,ISNULL(tBrowse.browseCount,0)browseCount from ( select CONVERT(nvarchar(20),'2017/5/16',23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-1,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-2,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-3,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-4,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-5,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-6,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-7,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-8,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-9,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-10,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-11,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-12,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-13,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-14,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-15,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-16,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-17,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-18,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-19,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-20,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-21,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-22,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-23,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-24,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-25,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-26,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-27,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-28,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-29,'2017/5/16'),23) Date
union select CONVERT(nvarchar(20),DATEADD(day,-30,'2017/5/16'),23) Date )tDate  
left join  (select convert(char(10),rep.REP_Addtime,120)Date,COUNT(1)addCount from Rep_ResearchReport rep  
join (select cmy.CompanyId from Cmy_Company cmy where cmy.CCO_status=1)cmy on rep.CompanyId=cmy.CompanyId  
group by convert(char(10),rep.REP_Addtime,120))tAdd on tDate.Date=tAdd.Date  
left join  (select convert(char(10),rep.REP_DownTime,120)Date,COUNT(1)downCount from Rep_ReportForm rep  
where exists(select r.ReportID from Rep_ResearchReport r
join(select cmy.CompanyId from Cmy_Company cmy where cmy.CCO_status=1)cmy on r.CompanyId=cmy.CompanyId
where r.ReportID=rep.ReportID )group by convert(char(10),rep.REP_DownTime,120))tDown on tDate.Date=tDown.Date  
left join  (select convert(char(10),rep.RBR_AddTime,120)Date,COUNT(1)browseCount from Rep_BrowseRecord rep  
where exists(select r.ReportID from Rep_ResearchReport r
join(select cmy.CompanyId from Cmy_Company cmy where cmy.CCO_status=1)cmy on r.CompanyId=cmy.CompanyId
where r.ReportID=rep.RBR_ReportID )group by convert(char(10),rep.RBR_AddTime,120))tBrowse on tDate.Date=tBrowse.Date  
order by tDate.Date asc

sql
半夏~微伤的主页 半夏~微伤 | 菜鸟二级 | 园豆:202
提问于:2017-05-19 15:22
< >
分享
所有回答(1)
0

把union all 的数据,存放在临时表中,或表变量中,

悦光阴 | 园豆:2094 (老鸟四级) | 2017-05-31 09:04
   您需要登录以后才能回答,未注册用户请先注册