Mysql 中的 max (length (field))

如果我说:

select max(length(Name))
from my_table

我得到的结果是18，但我也想要相关的数据，所以如果我说:

select max(length(Name)),
Name
from my_table

... 它不工作。应该有一个自我连接，我想我无法找到它。

有人能给我点提示吗？

155788 次浏览
小开

Edited, will work for unknown max() values:

select name, length( name )
from my_table
where length( name ) = ( select max( length( name ) ) from my_table );
小开

I suppose you could use a solution such as this one :

select name, length(name)
from users
where id = (
select id
from users
order by length(name) desc
limit 1
);

Might not be the optimal solution, though... But seems to work.

小开 
select *
from my_table
where length( Name ) = (
select max( length( Name ) )
from my_table
limit 1
);

It this involves two table scans, and so might not be very fast !

小开

Use:

  SELECT mt.name
FROM MY_TABLE mt
GROUP BY mt.name
HAVING MAX(LENGTH(mt.name)) = 18

...assuming you know the length beforehand. If you don't, use:

  SELECT mt.name
FROM MY_TABLE mt
JOIN (SELECT MAX(LENGTH(x.name) AS max_length
FROM MY_TABLE x) y ON y.max_length = LENGTH(mt.name)
小开 
SELECT  name, LENGTH(name) AS mlen
FROM    mytable
ORDER BY
mlen DESC
LIMIT 1
小开 
Select URColumnName From URTableName Where length(URColumnName ) IN
(Select max(length(URColumnName)) From URTableName);

This will give you the records in that particular column which has the maximum length.

小开

In case you need both max and min from same table:

    select * from (
(select city, length(city) as maxlen from station
order by maxlen desc limit 1)
union
(select city, length(city) as minlen from station
order by minlen,city limit 1))a;
小开

Ok, I am not sure what are you using(MySQL, SLQ Server, Oracle, MS Access..) But you can try the code below. It work in W3School example DB. Here try this: 

SELECT city, max(length(city)) FROM Customers;
小开

Use CHAR_LENGTH() instead-of LENGTH() as suggested in: MySQL - length() vs char_length()

SELECT name, CHAR_LENGTH(name) AS mlen FROM mytable ORDER BY mlen DESC LIMIT 1


提交答案