字段最短最长的MySQL查询

Question

问题

我需要从数据库中返回最短和最长的城市名称以及它们各自的长度。我还欢迎对此查询进行改进，以使其更加优雅。

SQL Fiddle

到目前为止，我有一个示例数据库&查询：http://sqlfiddle.com/#!9/3b4a7c/1/0

查询

SELECT DISTINCT City, LENGTH(City) as len
FROM STATION
WHERE LENGTH(City)=(SELECT MIN(LENGTH(City)) FROM STATION) 
OR LENGTH(City)=(SELECT MAX(LENGTH(City)) FROM STATION)
ORDER BY len

当前结果

Dole    4
Reus    4
Sant'Egidio del Monte Albino    28

期望结果

Dole    4
Sant'Egidio del Monte Albino    28

Answer 1

我会使用union all、order by和limit来完成这个任务。。。如果您只想要一个最长和最短的名称的例子：

(select city, length(city)
 from station
 order by length(city) asc
 limit 1
) union all
(select city, length(city)
 from station
 order by length(city) desc
 limit 1
);

如果你想打领带，当不止一个城市比赛的时候，我会：

select s.city, length(s.city)
from station s cross join
     (select max(length(city)) as maxl, min(length(city)) as minl
      from station
     ) ss
where length(s.city) in (ss.minl, ss.maxl);

Answer 2

select min(a.city),length(a.city)
from station a
join (SELECT min(length(city)) minlength,max(length(city)) maxlength from station) b
on length(a.city) in (minlength,maxlength)
group by length(a.city)