SELECT  [sJOB].[job_id] AS [作业ID] ,

        [sJOB].[name] AS [作业名] ,

        CASE WHEN [sJOBH].[run_date] IS NULL

                  OR [sJOBH].[run_time] IS NULL THEN NULL

             ELSE CAST(CAST([sJOBH].[run_date] AS CHAR(8)) + ' '

                  + STUFF(STUFF(RIGHT('000000'

                                      + CAST([sJOBH].[run_time] AS VARCHAR(6)),

                                      6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

        END AS [最近执行时间] ,

        CASE [sJOBH].[run_status]

          WHEN 0 THEN '失败'

          WHEN 1 THEN '成功'

          WHEN 2 THEN '重试'

          WHEN 3 THEN '取消'

          WHEN 4 THEN '正在运行' -- In Progress

        END AS [最近执行状态] ,

        STUFF(STUFF(RIGHT('000000'

                          + CAST([sJOBH].[run_duration] AS VARCHAR(6)), 6), 3,

                    0, ':'), 6, 0, ':') AS [LastRunDuration (HH:MM:SS)] ,

        [sJOBH].[message] AS [最近运行状态信息] ,

        CASE [sJOBSCH].[NextRunDate]

          WHEN 0 THEN NULL

          ELSE CAST(CAST([sJOBSCH].[NextRunDate] AS CHAR(8)) + ' '

               + STUFF(STUFF(RIGHT('000000'

                                   + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),

                                   6), 3, 0, ':'), 6, 0, ':') AS DATETIME)

        END AS [下次运行时间]

FROM    [msdb].[dbo].[sysjobs] AS [sJOB]

        LEFT JOIN ( SELECT  [job_id] ,

                            MIN([next_run_date]) AS [NextRunDate] ,

                            MIN([next_run_time]) AS [NextRunTime]

                    FROM    [msdb].[dbo].[sysjobschedules]

                    GROUP BY [job_id]

                  ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]

        LEFT JOIN ( SELECT  [job_id] ,

                            [run_date] ,

                            [run_time] ,

                            [run_status] ,

                            [run_duration] ,

                            [message] ,

                            ROW_NUMBER() OVER ( PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC ) AS RowNumber

                    FROM    [msdb].[dbo].[sysjobhistory]

WHERE   [step_id] = 0

                  ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id]

                                  AND [sJOBH].[RowNumber] = 1

ORDER BY [作业名]