在表格中查找最长和最短名称的SQL查询

Question

我有一张表，其中一个列的类型为varchar(city)，想要找到该列中存储的最长和最短值。

select a.city, a.city_length from (select city, char_length(city) city_length 
from station order by city, city_length) a
where a.city_length = (select min(a.city_length) from a) or
      a.city_length = (select max(a.city_length) from a)
group by a.city_length;

有人能帮忙吗？谢谢

---

一个解决方案：

select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length limit 1;
select * from (select city, char_length(city) city_length from station order by city, city_length) a group by a.city_length order by a.city_length desc limit 1;

- Michael Xu

---

你正在使用哪个关系型数据库管理系统？这对我们很重要，因为我们可以使用更高效的特定于RDBMS的功能（并向您提供一些高级技巧提示）。 - Insac

请注意，可能没有“最长的”名称，这意味着您可能有多个。 - YoYo

Answer 1

我认为我们不需要使用Min和Max函数，也不需要使用Group by。

我们可以使用以下代码实现：

select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC

select top 1 CITY, LEN(city) City_Length from station order by City_Length desc, City ASC

但在这种情况下，它将在 2 张表中显示输出，如果我们想要将它们合并到一个表中，则可以使用 Union 或 Union ALL。以下是相同内容的 SQL 查询

  select * from (
     select top 1 City, LEN(City) City_Length from STATION order by City_Length ASC,City ASC) TblMin
   UNION
   select * from (
   select top 1 CITY, LEN(city) City_Length from STATION order by City_Length desc, City ASC) TblMax

在这里，我将select语句嵌套在子查询中，因为当我们使用order by子句时，不能直接使用Union或Union ALL，所以我将其编写在子查询中。

- Banketeshvar Narayan

---

当出现如下语句ERROR at line 1: ORA-00904: "TOP": invalid identifier为Oracle错误。 - Hara

针对一个没有指定数据库管理系统的问题，给出特定产品的答案。请至少告诉我们这是哪个数据库管理系统的。 - jarlh

@jarlh 这是针对 SQL Server 的。 - Ashish Patel

Answer 2

最短：

select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity ASC, CITY ASC;

最长的：

select TOP 1 CITY,LEN(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity DESC, CITY ASC;

这适用于HackerRank挑战问题（MS SQL Server）。

- mrusom

---

如果我们选择Oracle，那么这个查询在Hacker Rank上就无法工作。它会抛出错误，错误信息为“第1行出现错误：ORA-00904: "TOP": 无效标识符”。 - Hara

这个查询是针对 MS SQL Server 的，不能在 Oracle 中使用。请尝试以下查询语句： 最短的： select * from (select CITY,LENGTH(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity ASC, CITY ASC) where rownum = 1;最长的： select * from (select CITY,LENGTH(CITY) LengthOfCity FROM STATION ORDER BY LengthOfCity DESC, CITY ASC) where rownum = 1; - mrusom

针对 MySql 数据库：查询语句 1： SELECT CITY, LENGTH(CITY) as LEN FROM STATION ORDER BY LEN ASC, CITY ASC LIMIT 1;查询语句 2： SELECT CITY, LENGTH(CITY) as LEN FROM STATION ORDER BY LEN DESC, CITY ASC LIMIT 1; - stormwild

LengthOfCity是什么...它是否像别名一样...在Hacker Rank上没有名为LengthOfCity的列名 - Shubham Jain

@ShubhamJain 你说得对，这是别名，定义后可在ORDER中使用。 - ogostos

Answer 3

在MySQL中

(select city, LENGTH(city) cityLength  from station order by cityLength desc,city asc LIMIT 1)
    union all
    (select city, LENGTH(city) cityLength  from station order by cityLength asc,city asc LIMIT 1)

- PyData

---

为什么要加上LIMIT 1？你能否解释一下？ - Taylor

@Taylor，LIMIT 1将查询结果限制为一个条目（在本例中为城市）。使用此查询，您可以获取名称最短和最长的城市。 - Alex_P

Answer 4

也许一个更简单的选择，因为我想你是在寻求对Hacker Rank问题的解决方案？添加限制条件使我更容易调试返回的错误信息，这样更加清晰明了。

SELECT city, length(city) FROM station order by length(city) desc limit 1;

SELECT city, length(city) FROM station order by length(city) asc, city asc limit 1

- RichKenny

Answer 5

您的查询只需要进行一些微调即可。根本问题在于，您不能像现在这样在子查询中使用a：

select a.city, a.city_length
from (select city, char_length(city) city_length 
      from station 
     ) a
where a.city_length = (select min(char_length(city)) from station) or
      a.city_length = (select max(char_length(city)) from station);

话虽如此，更简单的编写查询方式是：

select s.*
from station s cross join
     (select min(char_length(city)) as mincl, max(char_length(city)) as maxcl
      from station
     ) ss
where char_length(s.city) in (mincl, maxcl);

- Gordon Linoff

Answer 6

升序排序：

SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city), city LIMIT 1;

降序排序：

SELECT city, CHAR_LENGTH(city) FROM station ORDER BY CHAR_LENGTH(city) DESC, city LIMIT 1;

- nepete

Answer 7

在Oracle中：

选择*从(select城市，min(length(city)) minl from station group by city order by minl, city) where rownum = 1;
选择*从(select城市，max(length(city)) maxl from station group by city order by maxl desc, city) where rownum = 1;

- Alex L

Answer 8

这是在MySQL中另一种实现方式。可能不是最好的，但仍然是一个逻辑正确的选择。

select
   city,
   length(city) 
from
   station 
where
   length(city) in 
   (
      select
         max(length(city)) 
      from
         station 
      union
      select
         min(length(city)) 
      from
         station
   )
order by
   length(city) desc,
   city asc limit 2;

- 1218985

---

如果有人正在寻找 Hackerank 的解决方案，我可以证实这个解决方案在2022年6月仍然有效。 - fatfrog

Answer 9

获取城市名称的最短方式为：

SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH ASC, ST.CITY ASC
LIMIT 1;

对于城市名称最长的情况：

SELECT ST.CITY,LENGTH(ST.CITY) AS LENGTH FROM STATION ST
ORDER BY LENGTH DESC, ST.CITY DESC
LIMIT 1;

- AnksG

Answer 10

这是一种使用CTE的方法。首先找到最长和最短的城市，然后匹配它们：

DECLARE @tbl TABLE(CityName VARCHAR(100));
INSERT INTO @tbl VALUES ('xy'),('Long name'),('very long name'),('middle'),('extremely long name');

WITH MyCTE AS 
(
    SELECT MAX(LEN(CityName)) AS Longest
          ,MIN(LEN(CityName)) AS Shortest
    FROM @tbl
)
SELECT * 
FROM MyCTE
--You must think about the chance of more than one city matching the given length
CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Longest) AS LongestCity(LongName)
CROSS APPLY(SELECT TOP 1 CityName FROM @tbl WHERE LEN(CityName)=Shortest) AS ShortestCity(ShortName)

结果

Longest Shortest    LongName               ShortName
19       2          extremely long name    xy

- Shnugo