用于查找表中最长名称和最短名称的 SQL 查询

SQL query for finding the longest name and shortest name in a table

提问by Michael Xu

I have a table with one of the columns is of type varchar(city). and want to find the longest and shortest of values stored in that column.

我有一个表，其中一列的类型是 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;

Can anyone help? Thanks

任何人都可以帮忙吗？谢谢

---

---

One solution:

一种解决方案：

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;

回答by Banketeshvar Narayan

I don't think that we need to use Min and Max functions and Group by is also not required.

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

We can achieve this using the below code:

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

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

but in this case, it will display output in 2 tableand if we would like to combine in a single table then we can use Union or Union ALL. Below is the SQL query for the same

但在这种情况下，它将在 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

here I am nesting the select statement inside a subquery because when we are using order by clause then we cannot use Union or Union ALLdirectly that is why I have written it inside a subquery.

在这里，我将 select 语句嵌套在子查询中，因为当我们使用 order by 子句时，我们不能直接使用Union 或 Union ALL，这就是我将它写在子查询中的原因。

回答by mrusom

Shortest:

最短：

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

Longest:

最长：

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

This works for HackerRank challenge problem (MS SQL Server).

这适用于 HackerRank 挑战问题 (MS SQL Server)。

回答by RichKenny

Maybe a simpler option since I imagine you are looking for help with a solution to a Hacker Rank question? The addition of limits made it simpler for me to debug where the issue was with the returned error.

也许是一个更简单的选择，因为我想您正在寻求解决黑客排名问题的帮助？添加限制使我可以更轻松地调试问题所在的返回错误。

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

回答by PyData

In MySQL

在 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)

回答by Gordon Linoff

You query requires just a few tweaks. The fundamental problem is that you cannot use ain the subquery as you are doing:

您的查询只需要进行一些调整。根本问题是您不能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);

That said, a simpler way to write the query is:

也就是说，编写查询的更简单方法是：

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);

回答by Alex L

In Oracle:

在甲骨文中：

select * from (select city, min(length(city)) minl from station group by city order by minl, city) where rownum = 1;
select * from (select city, max(length(city)) maxl from station group by city order by maxl desc, city) where rownum = 1;

回答by nepete

Ascending:

上升：

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

Descending:

降序：

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

回答by Shnugo

This is an approach with a CTE. First it finds the longest and shortest, than the matching cities:

这是一种带有 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)

The result

结果

Longest Shortest    LongName               ShortName
19       2          extremely long name    xy

回答by helpMeLearn

I did this in SQL Server using CTE and dense_rank function. How the ranking works?

我在 SQL Server 中使用 CTE 和 density_rank 函数做到了这一点。排名如何运作？

First partition (form groups) over the lengths, i.e same lengths make a group (partition). Then order all the names alphabetically within each partition. Then assign ranks (dRank column) within each partition. So rank 1s in each group will be assigned to names which alphabetically appear first in their respective partition. All this happens in the common table expression (cte block)

长度上的第一个分区（形成组），即相同的长度构成一个组（分区）。然后按字母顺序排列每个分区内的所有名称。然后在每个分区内分配等级（dRank 列）。因此，每个组中的排名 1 将分配给按字母顺序出现在各自分区中的第一个名称。所有这些都发生在公共表表达式（cte 块）中

"with cte as
(
select *, LEN(city) as length, DENSE_RANK() over (partition by len(city) order by city) as dRank from Station
)"

select city,length from cte where dRank = 1 and length = (select MIN(length) from cte)
UNION
select city,length from cte where dRank = 1 and length = (select max(length) from cte)"

回答by umang gala

Initially finding the shortest length of the cityand taking an union with the longest length of the city. This minimizes the complexity of the query.

最初找到 的最短长度city并与 的最长长度取并集city。这最大限度地减少了查询的复杂性。

(select city, char_length(city) as len_city
from station
order by len_city limit 1)
union ( select city, char_length(city) as len_city
    from station
    order by len_city desc limit 1) 
order by len_city