分组时如何从表中选择最长的“字符串”

例如:

SELECT partnumber, manufacturer, condition, SUM(qty), AVG(price), description FROM parts


WHERE [something]


GROUP BY partnumber, manufacturer, condition

我有一些描述是空白的，可以有许多部件号码，制造商，条件值，并在组，它似乎采取第一个描述可用，这可以是空白的。我想要最长的描述。

我试过了:

MAX(LENGTH(description))

但是，它返回字符串中的字符数。有没有可能在 MySQL 中做我正在尝试做的事情？

80587 次浏览
小开
最佳答案

Try ORDER BY LENGTH(description) DESC and use LIMIT 1 to only get the largest.

小开

I found a solution. MAX(description) seems to work just fine.

小开 
SELECT   partnumber, manufacturer, `condition`, SUM(qty), AVG(price), description
FROM     parts
WHERE    [something] AND LENGTH(description) = (
SELECT MAX(LENGTH(description))
FROM   parts AS p
WHERE  p.partnumber   = parts.partnumber
AND p.manufacturer = parts.manufacturer
AND p.condition    = parts.condition
)
GROUP BY partnumber, manufacturer, `condition`
小开 
ORDER BY LENGTH(description) DESC LIMIT 1

This will sort the results from longest to shortest and give the first result (longest.)

小开

MAX(LENGTH(description)) returns length of longest value in Description column.

小开

If a "description" contains multi-idiom characters, then you should use

MAX(CHAR_LENGTH(description))

For example :

SELECT LENGTH("Это тест, связанный с длиной строки в базе данных")

Resulting in 89.

Whereas :

SELECT CHAR_LENGTH("Это тест, связанный с длиной строки в базе данных")

Resulting in 49.


提交答案