mysql – 左外连接的MAX条件

----------
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

任何帮助将不胜感激！ 最佳答案 您的原始查询仍然从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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!