需要替代解决方案来处理涉及CTE和临时表的查询 [英] Need alternative solution for the query involving CTE and temp tables

查看:90
本文介绍了需要替代解决方案来处理涉及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和其他方法)也无法满足相同的要求吗?

解决方案

我想到了相关的子查询。

< 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆