获取联接多个表的最新修改日期

Question

我有4个表

1. 项目表

   projectid: INT (pk)
   modifiedDate: DateTime2
   ...
   

2. 项目定稿表

   id: INT (pk)
   projectid: INT (fk)
   modifiedDate: DateTime2
   ...
   

3. 项目附加时间表

   id: INT (pk)
   projectid: INT (fk)
   modifiedDate: DateTime2
   ...
   

4. 项目完成表

   id: INT (pk)
   projectid: INT (fk)
   modifiedDate: DateTime2
   ...
   

任何表都会根据用户的操作进行修改。所有表的projectId都是ProjectTable中的foreignKey。

我想从任何一张表中获取最新的modifiedDate。例如：ProjectFinalizeTable可以有最新的modifiedDate，以便从查询中返回日期。

为了让你知道我想要什么，下面的查询是这样的(注意：下面的查询只是为了让你了解我到底在寻找什么(

Select MAX(pt.modifiedDate, pat.modifiedDate, pft.modifiedDate, pct.modifiedDate) as latestModifiedDate 
from ProjectTable pt 
join ProjectFinalizeTable pft pt.projectid = pft.projectid
join ProjectAdditionalTimeTable pat pt.projectid = pat.projectid
join ProjectCompleteTable pct pt.projectid = pct.projectid

我已经浏览了很多链接，但找不到从多个表中计算MAX(Date)的方法。

Answer 1

如果您希望max(modifiedDate)覆盖多个表，那么统一它们并聚合它们似乎是可行的。

with cte as (
select projectId, modifiedDate
from dbo.ProjectTable
union all
select projectId, modifiedDate
from dbo.ProjectFinalizeTable
union all
select projectId, modifiedDate
from dbo.ProjectAdditionalTimeTable
union all
select projectId, modifiedDate
from dbo.ProjectCompleteTable
)
select projectId, max(modifiedDate)
from cte
group by projectId;

Answer 2

你可以试试这个。虽然我无法验证。

SELECT 
PT.projectid,
(SELECT MAX(LastModifiedDate)
FROM (VALUES (PT.ModifiedDate),(PFT.ModifiedDate),(PATT.ModifiedDate), (PCT.ModifiedDate)) AS UpdateDate(LastModifiedDate)) 
AS LastModifiedDate
FROM
ProjectTable PT
JOIN ProjectFinalizeTable PFT ON PT.projectid = PFT.projectid
JOIN ProjectAdditionalTimeTable PATT ON PT.projectid = PATT.projectid
JOIN ProjectCompleteTable PCT ON PT.projectid = PCT.projectid