我需要加入 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'