Question

我要问:

   SELECT reply.id,
          reply.message,
          reply.userid,
          reply.date,
          medal.id,
          medal.url,
          medal.name,
          user.id,
          user.name AS username
     FROM posts AS reply
LEFT JOIN users AS user ON reply.userid = user.id
LEFT JOIN medals AS medal ON medal.userid = user.id
 GROUP BY reply.id
 ORDER BY reply.id ASC

everything is OK, except that I get the medal ascending not descending which mean that it grab the first medal that user got - I need to get the last one.

Answer 1

看到每个群体第一个记录是意外的。按组别选择一个非组类、非组别一栏,将产生一个未界定的记录。

详细解释如下:。 http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html。

你重新做些什么的正确方法是使用一个子座,在座标上,你选择每个理想群体的最高潮日。

本文概述了这一办法:

Answer 2

也许,你可以为奖牌提供分局或临时桌,而不是直接加入奖牌。临时表格是粉碎表,但分类为DESC。排序为LEFT JOIN(通过电离层、尿液、按 des子顺序排列的名字),但我感到,这样做非常缓慢,不是最佳选择。

Answer 3

最简单的解决办法是:

ORDER BY reply.id ASC, medal.id DESC

www.un.org/chinese/ga/president

你们可以过滤后有多个奖牌的岗位/用户(含有你不希望的奖牌的跳板)。

下一个选择是,在分局选择<代码>(中等),然后加入。这令人迷惑,但却是站不住脚的。这里是一般的想法(可能必须重新命名几个桌子,因为它们不止一次使用):

SELECT *,
       medal.url,
       medal.name
FROM
(
   SELECT reply.id,
          reply.message,
          reply.userid,
          reply.date,
          MAX(medal.id) AS medal_id,
          user.id,
          user.name AS username
     FROM posts AS reply
LEFT JOIN users AS user ON reply.userid = user.id
LEFT JOIN medals AS medal ON medal.userid = user.id
 GROUP BY reply.id
 ORDER BY reply.id ASC
)
AS t
LEFT JOIN medals AS medal ON medal.id = t.medal_id

友情链接