如何在另一個查詢中使用一個查詢的輸出

我正在使用以下查詢來獲取作為 JobId、JobName 和 Start Execution date 的輸出

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 列，該怎麼做。請建議

正如@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

引用自：https://dba.stackexchange.com/questions/150008