主页 / dba / 问题 / 150008
Accepted
joeprince
Asked: 2016-09-19 22:33:16 +0800 CST

如何在其他查询中使用一个查询的输出

  • 772

我正在使用以下查询将输出作为 JobId、JobName 和开始执行日期

 SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,      
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null

但是我想在另一个选择语句中使用 Start_Execution_Date 列，该怎么做。请提出建议

sql-server sql-server-2008

2 个回答

  1. 使用 CTE（公用表表达式）：

    with myquery as (
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,      
ja.last_executed_step_id
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh 
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j 
ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
AND start_execution_date is not null
stop_execution_date is null
)
SELECT * 
FROM myquery
JOIN ....

    文档链接。

    • 3
  2. Best Answer

    正如@phil 所提到的，您可以使用 CTE 并获得您的工作持续时间，您可以使用以下查询

    DECLARE @minutes_running    int,
WITH myquery (job_id , job_name , start_execution_date , last_executed_step_id)
AS
(
SELECT ja.job_id, 
       j.NAME AS job_name, 
       ja.start_execution_date, 
       ja.last_executed_step_id 
FROM   msdb.dbo.sysjobactivity ja 
       LEFT JOIN msdb.dbo.sysjobhistory jh 
              ON ja.job_history_id = jh.instance_id 
       JOIN msdb.dbo.sysjobs j 
         ON ja.job_id = j.job_id 
WHERE  ja.session_id = (SELECT TOP 1 session_id 
                        FROM   msdb.dbo.syssessions 
                        ORDER  BY agent_start_date DESC) 
       AND start_execution_date IS NOT NULL 
       AND stop_execution_date IS NULL 
       AND j.NAME = 'JOB_NAME' 
)

SELECT @minutes_running = DATEDIFF(minute,start_execution_date, getdate()) from myquery
SELECT @minutes_running as min
    • 0

相关问题