需要替代解决方案来处理涉及CTE和临时表的查询 [英] Need alternative solution for the query involving CTE and temp tables
问题描述
大家好
我需要显示从表格中存在的作业的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和其他方法)也无法满足相同的要求吗?
我想到了相关的子查询。
< pre lang =SQL> SELECT RUN_ID,JOB_NAME,START_TIME,END_TIME,
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_start
WHERE on_start.RUN_ID<> ; failed.RUN_ID
AND failed.START_TIME BETWEEN on_start.START_TIME AND on_start.END_TIME
) AS ' StartTime的JobsCount',
(
SELECT COUNT(*) FROM COMP_HIS_TBL on_end
WHERE on_end.RUN_ID&l t;> failed.RUN_ID
&失败.END_TIME BETWEEN on_end.START_TIME AND on_end.END_TIME
) AS ' EndTime的JobsCount'
FROM COMP_HIS_TBL失败
WHERE failed.STATUS = ' 失败'
我没有运行,所以可能存在一些错误。
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_tab order by 1,EXECUTION_TIME desc select @max=MAX(ROW_NUMBER) from #temp while (@count<=@max) begin select @stime=start_time,@endtime=end_time,@runid=run_id,@jobname=job_name from #temp where ROW_NUMBER=@count select @runid RUN_ID ,@jobname JOB_NAME,@starttime Start_time ,@endtime end_time select 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+1 end --DROP TABLE #TEMP
Which 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??
Correlated sub-queries come to my mind.
SELECT RUN_ID, JOB_NAME, START_TIME, END_TIME, ( SELECT COUNT(*) FROM COMP_HIS_TBL on_start WHERE on_start.RUN_ID <> failed.RUN_ID AND failed.START_TIME BETWEEN on_start.START_TIME AND on_start.END_TIME ) AS 'JobsCount at StartTime', ( SELECT COUNT(*) FROM COMP_HIS_TBL on_end WHERE on_end.RUN_ID <> failed.RUN_ID AND failed.END_TIME BETWEEN on_end.START_TIME AND on_end.END_TIME ) AS 'JobsCount at EndTime' FROM COMP_HIS_TBL failed WHERE failed.STATUS = 'Failure'
I didn't run this so there may be some bugs.
这篇关于需要替代解决方案来处理涉及CTE和临时表的查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!