mysql - sql查询以获取组的最大日期

我希望每个WishContent基于wishContent.wishId为最新的日期，所以，如果我有这样的wishContents：

ID   DATE
#1 : 09-03-2016
#1 : 08-03-2016
#1 : 03-04-2016
#2 : 09-02-2016
#2 : 04-01-2016

然后我只想要：

#1 09-03-2016
#2 09-02-2016

SELECT
      wish.Status,
      wish.Id,
      wish.User,
      wish.CompletionDate,
      wishContent.Content,
      wishContent.Title,
      wishContent.Country,
      wishContent.City,
      wishContent.IsAccepted,
      wishContent.moderator_Username,
      MAX(wishContent.Date) AS max_date
      FROM `wish` JOIN wishContent on wish.Id = wishContent.wish_Id
      GROUP BY wish.Id where wish.Date
      ORDER BY max_date DESC

谁能帮我 ？

最佳答案

我认为您需要额外的联接才能获得所需的结果：

SELECT
      w.Status,
      w.Id,
      w.User,
      w.CompletionDate,
      wc.Content,
      wc.Title,
      wc.Country,
      wc.City,
      wc.IsAccepted,
      wc.moderator_Username,
      wcMax.max_date
      FROM wish AS w
      JOIN (SELECT wish_Id, MAX(wishContent.Date) AS max_date
            FROM wishContent
            GROUP BY wish_Id
      ) AS wcMax ON w.Id = wcMax.wish_Id
      JOIN wishContent AS wc on wcMax.wish_Id = wc.wish_Id AND wc.Date = wcMax.max_date
      WHERE wish.Date
      ORDER BY max_date DESC