mysql– 左外连接的MAX条件

概述---------- samples ---------- SamplesID stylenumber stylename status ----------- samples_details ----------- Samples_Details_ID SamplesID CustomerName date_out date_returned updat

----------samples----------SamplesIDstylenumberstylenamestatus-----------samples_details-----------Samples_Details_IDSamplesIDCustomernamedate_outdate_returnedupdated (timestamp)status------------samples_pictures------------SamplesPicIDSamplesID

尝试编写一个查询,它给出了样本中的所有行,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

总结

以上是内存溢出为你收集整理的mysql – 左外连接的MAX条件全部内容，希望文章能够帮你解决mysql – 左外连接的MAX条件所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。