在 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.
你需要使用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( ) 与 LENGTH( ) https://stackoverflow.com/questions/1734334/mysql-length-vs-char-length
SQLFiddle 演示 http://sqlfiddle.com/#!2/d146d/2
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
