SQL 查询STATION中CITY名称最短和最长的两个城市，

Query the two cities in STATION with the shortest and longest CITY names,

提问by krishna beenavoina

Query: Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.

查询：查询STATION中最短和最长CITY名称的两个城市，以及它们各自的长度（即：名称中的字符数）。如果有多个最小或最大城市，请选择按字母顺序排列的第一个。

Sample Input :

样本输入：

Let's say that CITY only has four entries: DEF, ABC, PQRS and WXY

假设 CITY 只有四个条目： DEF, ABC, PQRS and WXY

Sample Output:

示例输出：

ABC 3
PQRS 4

回答by user7330784

TRY THIS :)

尝试这个 ：）

mysql code.... simple one

mysql代码....简单的一个

select CITY,LENGTH(CITY) from STATION order by Length(CITY) asc, CITY limit 1; 
select CITY,LENGTH(CITY) from STATION order by Length(CITY) desc, CITY limit 1; 

Edit:

编辑：

The above solution is not working for me as it doesn't sort alphabetically. As commented by @omottothe following is the proper way to make it work. I have tried in SQL server and it works.

上述解决方案对我不起作用，因为它没有按字母顺序排序。正如@omotto以下评论的那样，这是使其工作的正确方法。我已经在 SQL 服务器中尝试过，它可以工作。

select top 1 city, len(city) from station order by len(city) ASC, city ASC; 
select top 1 city, len(city) from station order by len(city) DESC, city ASC;

回答by Adithya_Kadri

( select CITY, 
       char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                          from STATION 
                          order by char_length(CITY) LIMIT 1) 
  Order by CITY LIMIT 1) 
 UNION ALL 
 (select CITY,
        char_length(CITY) as len_city 
  from STATION 
  where char_length(CITY)=(select char_length(CITY) 
                           from STATION 
                           order by char_length(CITY) DESC LIMIT 1)  
  Order by CITY DESC LIMIT 1) 
  ORDER BY char_length(CITY);

回答by radhikesh93

For MS SQL Server:

对于 MS SQL 服务器：

Declare @Small int
Declare @Large int
select @Small = Min(Len(City)) from Station 
select @Large = Max(Len(City)) from Station
select Top 1 City as SmallestCityName,Len(City) as Minimumlength from Station where Len(City) = @Small Order by City Asc
select Top 1 City as LargestCityName,Len(City) as MaximumLength from Station where Len(City) = @Large Order by City Asc

For Oracle server:

对于 Oracle 服务器：

select * from(select distinct city,length(city) from station order by length(city) asc,city asc) where rownum=1 union
select * from(select distinct city,length(city) from station order by length(city) desc,city desc) where rownum=1;

回答by Mike

select min(city), len
  from (
        select city, length(city) len,
               max(length(city)) over() maxlen,
               min(length(city)) over() minlen
          from station
       )
 where len in(minlen,maxlen)
 group by len

Subquery gets the list of cities and it's length. At the same time "window functions"min/max over()get minimal and maximal length for all rows in set (table). Main query filter only cities of length is min/max. min(city)with the group by lengives the result first name on the alphabetical order.

子查询获取城市列表及其长度。同时，“窗口函数”min/max over()获得集合（表）中所有行的最小和最大长度。主查询过滤器只有长度为最小/最大的城市。min(city)与 group bylen按字母顺序给出结果的名字。

回答by sstan

Here is another way to do it using the always handy row_numberanalytic function:

这是使用始终方便的row_number分析函数的另一种方法：

with cte as (
  select city,
         length(city) as len,
         row_number() over (order by length(city), city) as smallest_rn,
         row_number() over (order by length(city) desc, city) as largest_rn
    from station
)
select city, len
  from cte
 where smallest_rn = 1
union all
select city, len
  from cte
 where largest_rn = 1

回答by Ashish Sharma

SELECT city, LENGTH(city) FROM station  
WHERE LENGTH(city)=(SELECT MAX(LENGTH(city)) FROM station)
AND ROWNUM=1;

SELECT city, LENGTH(city) 
FROM station  
WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM STATION) 
AND ROWNUM=1
ORDER BY CITY;

回答by kjmerf

Try this one using UNION:

试试这个使用UNION：

SELECT MIN(city), LENGTH(city)
FROM Station
WHERE LENGTH(city) =
(SELECT MIN(LENGTH(city))
FROM Station)
UNION
SELECT MIN(city), LENGTH(city)
FROM Station
WHERE LENGTH(city) =
(SELECT MAX(LENGTH(city))
FROM Station)

Of course, my assumption is that your table name is Station and column name is City. See the related post below about only choosing the first record alphabetically:

当然，我的假设是您的表名是 Station，列名是 City。请参阅下面有关仅按字母顺序选择第一条记录的相关帖子：

Return only the first alphabetical result of SELECT

仅返回 SELECT 的第一个字母结果