GrList 基础表 Grclass、Grcode Glog2 ,Gdcode、Gdclass、Gdate 记录表,数据量大,可能上百万。 以GrList 为基础查询关联Glog2 最近一次记录 我要写法如下, 还有什么提高效率的不??? select a.* from Glog2 a inner join (select Gdclass,Gdcode,MAX(Gdate) 'Gdate' from Glog2 GROUP BY Gdclass,Gdcode) b on a.Gdclass=b.Gdclass and a.Gdcode=b.Gdcode and a.Gdate=b.Gdate left join GrList gl ON a.Gdcode=gl.Grcode and a.Gdclass=gl.Grclass where gl.Grgroup='G02'
----------------------------------------------
free
首先,确保Glog2表和GrList表有对Gdclass,Gdcode做索引,如果没有的话先做索引: CREATE INDEX Index_Gdclass_Gdcode_Gdate ON Glog2 (Gdclass, Gdcode, Gdate); CREATE INDEX Index_Gdclass_Gdcode ON GrList (Gdclass, Gdcode);
然后执行下列语句组: 然后执行下列语句组: SELECT Gdclass, Gdcode, MAX(Gdate) AS Gdate INTO #tmp FROM Glog2 GROUP BY Gdclass, Gdcode; CREATE CLUSTERED INDEX Index_Gdclass_Gdcode_Gdate ON #tmp (Gdclass, Gdcode, Gdate); SELECT a.* FROM GrList g JOIN #tmp t ON t.Gdclass=g.Gdclass and t.Gdcode=g.Gdcode AND t.Gdate=g.Gdate JOIN Glog2 a ON a.Gdclass=g.Gdclass and a.Gdcode=g.Gdcode WHERE g.Grgroup='G02'; DROP TABLE #tmp;
首先,确保Glog2表和GrList表有对Gdclass,Gdcode做索引,如果没有的话先做索引: CREATE INDEX Index_Gdclass_Gdcode_Gdate ON Glog2 (Gdclass, Gdcode, Gdate); CREATE INDEX Index_Gdclass_Gdcode ON GrList (Gdclass, Gdcode);
然后执行下列语句组: 然后执行下列语句组: SELECT Gdclass, Gdcode, MAX(Gdate) AS Gdate INTO #tmp FROM Glog2 GROUP BY Gdclass, Gdcode; CREATE CLUSTERED INDEX Index_Gdclass_Gdcode_Gdate ON #tmp (Gdclass, Gdcode, Gdate); SELECT a.* FROM GrList g JOIN #tmp t ON t.Gdclass=g.Gdclass and t.Gdcode=g.Gdcode JOIN Glog2 a ON a.Gdclass=t.Gdclass and a.Gdcode=t.Gdcode AND a.Gdate=t.Gdate --更正 WHERE g.Grgroup='G02'; DROP TABLE #tmp;