显示前1最大列
问题描述:
我有一个查询获取有关特定作业的一些信息。显示前1最大列
但是,我努力获得每项工作的最高记录(基于最新的'LastRun'字段)。
任何帮助我如何实现这一目标?
我的查询是:
SELECT
job.Name,
CAST(CAST(hist.run_date AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST(hist.run_time AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME) AS LastRun,
CASE hist.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status,
CASE job.enabled
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS Enabled
FROM dbo.sysjobhistory hist
JOIN dbo.sysjobs_view job ON job.job_id=hist.job_id
WHERE job.[name] = '[Parallel] Copy DBOutboxMaster'
OR job.[name] = '[Parallel] Copy filestore to PPE'
OR job.[name] = '[Parallel] copyLogShippingForECW'
OR job.[name] = '[Parallel] copyLogShippingForNLASQL'
GROUP BY job.name,hist.run_date,hist.run_time,hist.run_status,job.enabled
ORDER BY lastrun DESC
我目前得到的结果:
[Parallel] Copy DBOutboxMaster 2017-10-20 09:32:00.000 Success True
[Parallel] Copy filestore to PPE 2017-10-20 09:32:00.000 Success True
[Parallel] copyLogShippingForECW 2017-10-20 09:32:00.000 Success True
[Parallel] copyLogShippingForNLASQL 2017-10-20 09:31:11.000 Success True
[Parallel] Copy DBOutboxMaster 2017-10-20 09:31:00.000 Success True
正如你可以看到有每个作业重复字段。我只想每个工作的最高记录,所以总共应该只有4个最新LastRun日期时间的结果
答
感谢George Menoutis,对此的回答在以下查询中,通过将运行日期和状态栏:
SELECT
job.Name,
MAX(CAST(CAST(hist.run_date AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST(hist.run_time AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME)) AS LastRun,
MAX(CASE hist.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END) AS STATUS,
CASE job.enabled
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS Enabled
FROM dbo.sysjobhistory hist
JOIN dbo.sysjobs_view job ON job.job_id=hist.job_id
WHERE job.[name] = '[Parallel] Copy DBOutboxMaster'
OR job.[name] = '[Parallel] Copy filestore to PPE'
OR job.[name] = '[Parallel] copyLogShippingForECW'
OR job.[name] = '[Parallel] copyLogShippingForNLASQL'
GROUP BY job.name,job.enabled
ORDER BY lastrun DESC
结果:
[Parallel] Copy DBOutboxMaster 2017-10-20 09:43:00.000 Success True
[Parallel] Copy filestore to PPE 2017-10-20 09:43:00.000 Success True
[Parallel] copyLogShippingForECW 2017-10-20 09:32:00.000 Success True
[Parallel] copyLogShippingForNLASQL 2017-10-20 09:31:11.000 Success True
答
使用TOP 1 WITH TIES + ROW_NUMBER:
SELECT TOP 1 WITH TIES *
FROM (
SELECT job.[Name],
CAST(CAST(hist.run_date AS CHAR(8))
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST(hist.run_time AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS DATETIME) AS LastRun,
CASE hist.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status,
CASE job.enabled
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS Enabled
FROM dbo.sysjobhistory hist
JOIN dbo.sysjobs_view job ON job.job_id=hist.job_id
WHERE job.[name] = '[Parallel] Copy DBOutboxMaster'
OR job.[name] = '[Parallel] Copy filestore to PPE'
OR job.[name] = '[Parallel] copyLogShippingForECW'
OR job.[name] = '[Parallel] copyLogShippingForNLASQL'
GROUP BY job.name,hist.run_date,hist.run_time,hist.run_status,job.enabled
) as t
ORDER BY ROW_NUMBER() OVER (PARTITION BY [Name] ORDER BY lastrun DESC)
答
CROSS APPLY是 “每行顶部(n)的” 有用要求
SELECT
job.Name
, ca.LastRun
, ca.Status
, CASE job.enabled
WHEN 0 THEN 'False'
WHEN 1 THEN 'True'
END AS Enabled
FROM dbo.sysjobs_view job
CROSS APPLY (
SELECT TOP(1)
dateadd(day,datediff(day,0,run_date),cast(run_time as datetime)) AS LastRun
, CASE hist.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Canceled'
WHEN 4 THEN 'In progress'
END AS Status
FROM dbo.sysjobhistory hist
ORDER BY dateadd(day,datediff(day,0,run_date),cast(run_time as datetime)) DESC
WHERE hist.job_id = job.job_id
) AS ca
WHERE job.[name] = '[Parallel] Copy DBOutboxMaster'
OR job.[name] = '[Parallel] Copy filestore to PPE'
OR job.[name] = '[Parallel] copyLogShippingForECW'
OR job.[name] = '[Parallel] copyLogShippingForNLASQL'
ORDER BY ca.LastRun DESC
;
EDIT
要添加单独的列的类型日期&时间在一起,一个简单的/快速的方法是计算从零日期开始的天数,然后在将时间值转换为datetime之后将其添加到时间。
dateadd(day, datediff(day,0,run_date) ,cast(run_time as datetime))
使用基于数字的日期函数意味着你不依赖于服务器的设置来解释字符串转换为日期,和他们比转换/从字符串更快。
我不明白你的复制作业在哪里 –
从group by中删除hist.run_time,在select中将其更改为max(或min?)(hist.run_time)。那工作? –
@RyanGadsdon查看结果中的第一条记录和最后一条记录。他们是一样的工作 – Bodz