mysql - 左外连接的 MAX 条件

Question

----------
samples
----------
SamplesID
stylenumber
stylename
status

-----------
samples_details
-----------
Samples_Details_ID
SamplesID
CustomerName
date_out
date_returned
updated (timestamp)
status

------------
samples_pictures
------------
SamplesPicID
SamplesID

尝试编写一个查询，为我提供来自示例的所有行、来自 samples_pictures 的所有匹配项(如果有)以及来自 samples_details 的最新记录(如果有)。所以两个左外连接，但一个只检索最近的记录。

什么不起作用:

SELECT samples.*
     , samples_pictures.SamplesPicID
     , CustomerName
     , date_out
     , date_returned
     , updated
     , samples_details.status as txn_status 
FROM samples 
    LEFT OUTER JOIN
      ( SELECT Samples_Details_ID
             , samples_details.status as txn_status
             , MAX(updated) as MaxUpdated
        FROM samples_details
        GROUP BY Samples_Details_ID
      ) AS MaxTable
      ON MaxTable.SamplesID = samples.SamplesID 
    LEFT OUTER JOIN samples_pictures
      ON samples.SamplesID = samples_pictures.SamplesID

如有任何帮助，我们将不胜感激!

Answer 1

您的原始查询仍然从 samples_details 中选择每条 记录。

您必须创建一个子选择以仅过滤掉那些具有最大时间戳的记录，并将其与您的 samples_details 表连接起来以获得额外的列。

SELECT   *
FROM     samples s
         LEFT OUTER JOIN sample_details sd ON sd.SamplesID = s.SamplesID
         INNER JOIN (
           SELECT   SamplesID
                    , MAX(update) AS Update
           FROM     samples_details
           GROUP BY
                    SamplesID
         ) sdm ON sdm.SamplesID = sd.SamplesID
         LEFT OUTER JOIN samples_pictures sp ON sp.SamplesID = s.SamplesID