SQL Server大数据量统计系统的经验总结

184                from #temp1 a
185                group by a.'+ @groupby+';
186                DROP TABLE #temp1;
187                SELECT * FROM #TG009;
188                DROP TABLE #TG009'
189        
190    else if        @TE_MONI_T = '2'    --现场拒单
191            set @sql = '
192            select top '+convert(varchar(20),@END_ID)+ ' '+ @groupby +',IDENTITY(int,1,1) AS TID into #PAGE  from TG_ENTRY where '+@sqlWhere +' group by '+@groupby+ ';
193            CREATE TABLE #TG009(            
194                '+ @groupby+' VARCHAR(10) PRIMARY KEY NOT NULL,
195                RE_COUNT int null,
196                RE_TOTAL int null,
197                RE_PCT numeric(10,2) null,
198                MU_SEC_EXP numeric(10,2) null,
199                RE_TOTAL1  int null,
200                RE_TOTAL2  int null,
201                RE_TOTAL2B  int null
202            );
203            SELECT '+ @groupby+',TE_SCENE_TIME,TE_CANCEL_FLAG,TE_SCENE_FLAG,TE_MEET_FLAG INTO #temp1 FROM TG_ENTRY where'+@sqlWhere+' AND '+@groupby+' in ( select  '+ @groupby +' from #PAGE where TID between '+ convert(varchar(20),@START_ID)+ ' AND '+ convert(varchar(20),@END_ID)+');
204            INSERT INTO #TG009(
205                    '+ @groupby+',
206                    RE_COUNT,
207                    RE_TOTAL,
208                    RE_PCT,
209                    MU_SEC_EXP,
210                    RE_TOTAL1,
211                    RE_TOTAL2,
212                    RE_TOTAL2B) 
213            SELECT a.'+ @groupby+',
214            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'),
215            (SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'),
216            risk.HgDiv((SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+'),(SELECT COUNT(*) FROM #temp1 WHERE TE_MEET_FLAG = 1 and '+ @groupby+' =a.'+ @groupby+')),
217            risk.HgDiv((SELECT SUM(TE_SCENE_TIME) FROM #temp1 WHERE TE_SCENE_FLAG = 1  and '+ @groupby+' =a.'+ @groupby+'),(SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1  and '+ @groupby+' =a.'+ @groupby+')),
218            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 1 AND '+ @groupby+' =a.'+ @groupby+'),
219            (SELECT COUNT(*) FROM #temp1 WHERE TE_SCENE_FLAG = 1 AND TE_SCENE_TIME = 2 AND '+ @groupby+' =a.'+ @groupby+'),

上一页 1 2 3 4 5 67 下一页

评论总数：1 条 [ 查看全部 ] 网友评论