在MySQL中使用GROUP BY时，如何选择最长的文本字段？ [英] How can I select the longest text field when using GROUP BY in mysql, a la MAX()?

问题描述

在MySql中，当使用 GROUP BY 时，您可以使用 MAX（）函数获取最高值，我可以做同样的事情来获得最长的字符串吗？

In MySql you can use the MAX() function to get the highest value when using GROUP BY, how can I do the same thing to get the longest string of text?

样本表：

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 。

The tricky part is that I want to ORDER BY id ASC to see the oldest entries on the top, and I want to group by the post_id which is not something that I can use to ORDER, and get the longest 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 保留订单。

Again the key is to select the longest body while maintaining the order based on the id.

推荐答案

使用 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

您可能想看到不同：

You might want to see the difference: CHAR_LENGTH( ) vs LENGTH( )

这篇关于在MySQL中使用GROUP BY时，如何选择最长的文本字段？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！