mysql - 从不同的表中选择字段

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]

收到诸如无法绑定(bind)多部分标识符“A.max_LastUpdatedOn”之类的错误。

Answer 1

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

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

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

更新

关于无法绑定(bind)多部分标识符的错误，它与按不在from/join中的内容进行分组有关 查询来源。

我还没有尝试过下面的代码，但类似这样的代码应该可以解决问题:
(另外，请参阅此处有关如何选择 SQL MAX of multiple columns? 的更多方法)

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]