☰

加入 2 个 mssql 查询

tag-icon • tag-icon sql ms-sql
加入 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'

相关内容