如何获得最近工作的完成日期为空的场所的最新工作完成日期

Question

(精简(作业表包含字段：job.id、job.premises_id、job.completed_date

我需要知道job.comcompleted_date为空的场所(作业已预订但尚未完成(，输出必须包括该场所的最大值(job.completed_date((在该场所完成的最近作业的日期。

我试过了：

SELECT job.premises_id
, job.jobid
,(SELECT MAX(job.completed_date) FROM job where job.completed_date IS NOT NULL) as 'last job'
FROM job
WHERE job.completed_date is null

Answer 1

也许是具有分区聚合的CTE？

WITH CTE AS(
SELECT j.premises_id,
j.jobid,
j.completed_date,
MAX(j.completed_date) OVER (PARTITION BY j.premises_id) AS MaxCompletedDate
FROM dbo.job j)
SELECT premises_id,
jobid,
MaxCompletedDate
FROM CTE
WHERE completed_date IS NULL;

Answer 2

示例数据将有助于您的问题，但假设作业ID可以有多行，则需要在子查询中包含相关性Max已排除空值。

select j.premises_id, j.jobid, (
select Max(j2.completed_date) 
from job j2
where j2.jobId = j.JobId
) as [last job]
from job j
where j.completed_date is null;

您可能还需要在相关性中包含premise_id。还要注意，分隔符应该是方括号或双引号。