php - 使用多个值的内连接查询

Question

我的表结构如图:

table:App
|AppID|AppName|AppType|
   1    test     new

table:AppRelease
|AppReleaseID|AppID|ReleaseDate|ReleaseVersion|
   1            1   2012-06-20        2.2
   2            1   2012-06-19        2.3

我写了一个如下所示的查询:

SELECT A.*,B.ReleaseDate,B.ReleaseVersion 
FROM App as A 
  LEFT JOIN AppRelease as B ON A.AppID=B.AppID

此查询适用于 AppRelease 表中的单个值，但 AppRelease 表中的多个值我想获得最后添加的值。是否可以在单个查询中？

Answer 1

SELECT aa.*, bb.AppReleaseID, bb.ReleaseDate
FROM App aa LEFT JOIN (
            SELECT a.AppID, a.AppReleaseID, a.ReleaseDate
            FROM AppRelease a INNER JOIN (
                        SELECT AppID, MAX(ReleaseDate) mx FROM AppRelease 
                        GROUP BY AppID
                    ) b ON a.AppID = b.AppID AND a.ReleaseDate = b.mx
        ) bb ON bb.AppID = aa.AppID

fiddle :http://sqlfiddle.com/#!2/befa2/3