需要替代解决方案来处理涉及CTE和临时表的查询

本文介绍了需要替代解决方案来处理涉及CTE和临时表的查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 大家好 我需要显示从表格中存在的作业的Start_time和End_time开始的作业数量。表格结构包括： RepoName | JobName | RunID | StartTime | EndTime | ExecutionTime | Status | 预期输出应采用以下格式： - RunID | JobName | Start_time | End_Time |在StartTime的JobsCount |在EndTime的JobsCount | 我尝试了什么： 以下是查询尝试，涉及CTE和临时表： - 声明@count int = 1， @max int， @stime datetime， @endtime datetime， @runid int， @jobname varchar（1000） ; ，cte为（ 选择不同的RUN_ID，JOB_NAME，START_TIME，END_TIME，EXECUTION_TIME来自COMP_HIS_TBL（nolock） 其中STATUS ='失败' ） ，temp_tab为（ 选择ROW_NUMBER（）结束（按运行顺序排列）row_number，*来自cte） 从temp_tab中选择*进入#temp 顺序为1，EXECUTION_TIME desc 从#temp 选择@ max = MAX（ROW_NUMBER）while（@ count< = @ max） begin 从#temp中选择@ stime = start_time，@ endtime = end_time，@ runid = run_id，@ jobname = job_name，其中 ROW_NUMBER = @count select @runid RUN_ID，@ jobname JOB_NAME，@ starttime Start_time，@ endtime end_time 从COMP_HIS_TBL中选择计数（RUN_ID）为JOBS_AT_START_TIME（nolock） 其中@stime在START_TIME和END_TIME之间 从COMP_HIS_TBL（nolock）中选择计数（RUN_ID）为JOBS_AT_END_TIME 其中，在START_T_T之间的@endtime IME和END_TIME 套装@count = @count + 1 结束 - DROP TABLE #TEMP 哪些工作正常，但最终出现警告查询已完成但有错误!!最终几乎没有1行检索。请提供任何替代解决方案。其他方式（例如Joins和其他方法）也无法满足相同的要求吗？解决方案 Hi All I need to display the count of no of jobs starting at the Start_time and End_time of the jobs present in the table.The table structure consists of :RepoName|JobName | RunID | StartTime|EndTime |ExecutionTime|Status|The Expected output should be in the format:-RunID|JobName|Start_time|End_Time|JobsCount at StartTime|JobsCount at EndTime|What I have tried:Following was query tried which involves CTE and Temp tables:-declare @count int=1,@max int,@stime datetime,@endtime datetime,@runid int,@jobname varchar(1000);with cte as(select distinct RUN_ID,JOB_NAME,START_TIME,END_TIME,EXECUTION_TIME from COMP_HIS_TBL(nolock)where STATUS='Failure'),temp_tab as(select ROW_NUMBER() over(order by run_id)row_number,* from cte)select * into #temp from temp_taborder by 1,EXECUTION_TIME descselect @max=MAX(ROW_NUMBER) from #tempwhile (@count<=@max)beginselect @stime=start_time,@endtime=end_time,@runid=run_id,@jobname=job_name from #temp whereROW_NUMBER=@countselect@runid RUN_ID ,@jobname JOB_NAME,@starttime Start_time ,@endtime end_timeselect count(RUN_ID) as JOBS_AT_START_TIME from COMP_HIS_TBL(nolock)where @stime between START_TIME and END_TIME select count(RUN_ID) as JOBS_AT_END_TIME from COMP_HIS_TBL(nolock)where @endtime between START_TIME and END_TIME set @count=@count+1end--DROP TABLE #TEMPWhich is working fine for few jobs, but ending up with the warning "Query completed with errors!! and ending up with hardly "1 row retrieved". Please provide any alternative solution to this. The same requirement is not possible by other ways like Joins and others?? 解决方案 这篇关于需要替代解决方案来处理涉及CTE和临时表的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！