从不同的表中选择字段

Question

我需要从不同的数据库中选择不同的字段。其中一个字段是从不同的表中选择LastUpdateOn日期时间字段的最大值。

｛Project｝、｛Document｝、｝Instruction｝、{Document}、｛User｝和｛ProjectParticipant｝是不同的表

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],{A}.[max_LastUpdatedOn]
From {Project}
INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON {ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {InstructionType} ON {Project}.[Id] = {Instruction}.[ProjectId]
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
SELECT MAX(LastUpdatedOn) AS max_LastUpdatedOn
FROM 
(
  SELECT LastUpdatedOn FROM {Question}
  UNION ALL
  SELECT LastUpdatedOn FROM {Document}
  UNION ALL
  SELECT LastUpdatedOn FROM {Instruction}
) A;
GROUP BY {Project}.[Number],
         {Project}.[Name],
         {User}.[Last_Login]
         {A}.[max_LastUpdatedOn]
ORDER BY {Project}.[Number]

但是得到一个错误，说｛A｝是一个未知实体。我不确定这里出了什么问题。尝试使用

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],
(SELECT MAX(LastUpdatedOn) as max_LastUpdatedOn
 FROM
 (
  SELECT {Question}.[LastUpdatedOn] where {Project}.[Id] = {Question}.[ProjectId]
  UNION ALL
  Select {Document}.[LastUpdatedOn] where {Project}.[Id] = {Document}.[ProjectId]
  UNION ALL
  SELECT {Instruction}.[LastUpdatedOn] where {Project}.[Id] = {Instruction}.[ProjectId]
  )
  A ) [max_LastUpdatedOn]
 FROM {Project}
 INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
 INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
 INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
 INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
 INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]
 GROUP BY {Project}.[Number],
          {Project}.[Name],
          {User}.[Last_Login],
          A.[max_LastUpdatedOn]
 ORDER BY {Project}.[Number]

无法绑定多部分标识符"A.max_LastUpdatedOn"等错误。

Answer 1

{A}不是OutSystems实体，因此不应使用大括号来引用它。大括号用于从设计时的元模型名称翻译为数据库中的物理表名称。

由于A是内部select语句的一个简单别名，因此仅使用A就足以使其工作。例如：

SELECT {Project}.[Number],{Project}.[Name],{User}.[Last_Login],A.[max_LastUpdatedOn]

更新

关于The multi-part identifier could not be bound的错误，它与通过不在查询的from/join源中的东西进行分组有关。

我还没有尝试过下面的代码，但像这样的代码应该可以做到：
（另外，请参阅此处有关如何选择多列的SQL MAX的更多方法？）

SELECT {Project}.[Number], {Project}.[Name], {User}.[Last_Login], 
    CASE
        WHEN {Question}.[LastUpdatedOn] >= {Document}.[LastUpdatedOn] AND {Question}.[LastUpdatedOn] >= {Instruction}.[LastUpdatedOn]
        THEN {Question}.[LastUpdatedOn]
        WHEN {Document}.[LastUpdatedOn] >= {Instruction}.[LastUpdatedOn] THEN {Document}.[LastUpdatedOn]
        ELSE {Instruction}.[LastUpdatedOn]
    END AS max_LastUpdatedOn
FROM {Project}
INNER JOIN {ProjectParticipant} ON {Project}.[Id] = {ProjectParticipant}.[ProjectId]
INNER JOIN {User} ON ({ProjectParticipant}.[UserId] = {User}.[Id] AND {User}.[Username] = @UserId)
INNER JOIN {Question}  ON {Project}.[Id] = {Question}.[ProjectId]
INNER JOIN {Document} ON {Project}.[Id] = {Document}.[ProjectId]
INNER JOIN {Instruction} ON {Project}.[Id] = {Instruction}.[ProjectId]
GROUP BY {Project}.[Number], 
         {Project}.[Name], 
         {User}.[Last_Login]
ORDER BY {Project}.[Number]