加入 2 个 mssql 查询

加入 2 个 mssql 查询

我需要加入 MSSQL 查询,但是没有任何进展。

两个查询都返回单个值:

 SELECT COUNT(*) FROM sysjobhistory WHERE run_status <> 1 AND step_id = 0;

 SELECT MAX (run_date) FROM sysjobhistory WHERE run_status = 1 AND step_id = 0;

我完全是 SQL 菜鸟,所以任何帮助我都感激不尽

答案1

很难说出你想要的输出是什么,但我认为这会对你有所帮助:

DECLARE @Count INT
DECLARE @MaxRunDate DateTime -- I'm guessing run_date is a datetime... 

SELECT @Count = COUNT(*)
    FROM sysjobhistory WHERE run_status <> 1 AND step_id = 0;
SELECT @MaxRunDate = MAX(run_date)
    FROM sysjobhistory WHERE run_status = 1 AND step_id = 0;

SELECT @Count, @MaxRunDate

还有其他方法可以做到这一点,包括使用 JOIN,但我相信上面的方法更容易理解,特别是对于初学者而言。

答案2

如果要合并,请命名列:

SELECT COUNT(*) AS 'Column' FROM sysjobhistory WHERE run_status <> 1 AND step_id = 0
 UNION
SELECT MAX (run_date) AS 'Column' FROM sysjobhistory WHERE run_status = 1 AND step_id = 0

如果您希望在同一个查询中使用两列,则可以使用子查询:

SELECT (SELECT COUNT(*) FROM sysjobhistory WHERE run_status <> 1 AND step_id = 0) AS 'Count', (SELECT MAX (run_date) AS 'Column' FROM sysjobhistory WHERE run_status = 1 AND step_id = 0) AS 'Max'

相关内容