mysql - 在mysql中使用GROUP BY时如何选择最长的文本字段，a la MAX()？

Question

在 MySql 中你可以使用 MAX() 函数在使用 GROUP BY 时获取最大值，我怎样才能做同样的事情来获取最长的字符串文字？

示例表:

id_|_post_id|_title__________|_body_____________________________________________
1  | ZXBF1J | Favorite Color | My favorite color is blue.
2  | ZXBF1J | Favorite Color | My favorite color is blue, no wait...
3  | ZXBF1J | Favorite Color | My favorite color is blue, no wait, yelloooow!
4  | AR3D47 | Quest          | To seek..
5  | AR3D47 | Quest          | To seek the Holy
6  | AR3D47 | Quest          | To seek the Holy Grail.

棘手的部分是我想 ORDER BY id ASC 以查看顶部最旧的条目，并且我想按 post_id 进行分组，这不是什么我可以用它来ORDER，得到最长的body。

示例查询:

SELECT post_id, title, MAX(body) // obviously MAX() doesn't work here
FROM posts
GROUP BY post_id
ORDER BY id ASC

期望的输出:

post_id|_title__________|_body_____________________________________________
ZXBF1J | Favorite Color | My favorite color is blue, no wait, yelloooow!
AR3D47 | Quest          | To seek the Holy Grail.

再次关键是选择最长的body，同时保持基于id的顺序。

Answer 1

您需要使用 CHAR_LENGTH 而不是 LENGTH

SELECT a.id, a.post_id, a.body
FROM posts a INNER JOIN
(
    SELECT post_ID, title, MAX(CHAR_LENGTH(body)) totalLength
    FROM posts
    GROUP BY post_ID, title
) b ON a.post_id = b.post_ID AND
        a.title = b.title AND
        CHAR_LENGTH(a.body) = b.totalLength

您可能想看看区别: CHAR_LENGTH( ) vs LENGTH( )

SQLFiddle Demo