如何解决查询问题？ [英] How do I solve the problem with my query?

问题描述

大家好

需要有关查询的帮助。查询需要显示作业的并发列表。该表包含以下字段： -

Hi All
Needed help regarding a query.The query needs to display the concurrency list for jobs. The table has the following fields:-

Job_Name | Start_time | End_time | Execution_time | status

我必须显示那些工作的列表，这些工作的开始时间小于所选择的特定工作的开始时间，最后时间小于，作业的当前End_time。我试图使用CTE，但似乎是逻辑错误，并且需要很长时间才能执行查询。



START_TIME和END_TIME都是组合日期时间格式：-2016-04-30 00:32:40 00000000

我不知道如何在此论坛中附加截图！ :(



我的尝试：

I got to display the list of those jobs which started at a time lesser than the start time of a specific job selected and ends up in a time less than, the current End_time of the job.I tried to use a CTE for this, but seems like logical error, And its taking up a very long time to execute the query.

Both START_TIME AND END_TIME Are in combined date-time format:-2016-04-30 00:32:40 00000000
I am not sure how to attach screenshots in this forum as well! :(

What I have tried:

WITH TIME_CTE(JOB_NAME,START_TIME,END_TIME,ST_TIME,E_TIME)
As
-- Define the CTE query.
(
    SELECT JOB_NAME,START_TIME,END_TIME,CAST(START_TIME as time)as ST_TIME, CAST(END_TIME as time) as E_TIME
    FROM COMP_HIS_TBL where START_TIME like '2016-04-30%'
)
-- Define the outer query referencing the CTE name.
SELECT *
FROM TIME_CTE
JOIN (Select JOB_NAME,CAST(START_TIME as Time) as STRT_TIME , CAST(END_TIME as time) as ED_TIME from [COMP_HIS_TBL])as A 
ON TIME_CTE.ST_TIME

推荐答案

要获取在执行其他工作期间运行的工作列表，您可以使用简单的连接：

To get a list of jobs that were running during the execution of another job you can use a simple join:

declare @job varchar(100) = 'main'

select b.* 
from COMP_HIS_TBL a
inner join COMP_HIS_TBL b
on b.Start_Time <= a.End_Time
and b.End_Time >= a.Start_Time
and b.Job_name <> a.Job_Name
where a.Job_name = @job

a 是主要工作， b 是在此期间运行的所有其他工作。您只需要比较开始和结束时间。请注意，如果您考虑空值，例如尚未完成的作业，则会变得更复杂。



这是 SQL小提琴 [ ^ ]。

a is the main job, b are all the other jobs that were running during that time. You just need to compare the start and end times. Please note that this gets more complicated if you consider null values, jobs that didn't finish yet for example.

Here is the SQL Fiddle[^].

这篇关于如何解决查询问题？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！