用于查找表中最长名称和最短名称的 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 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

这是一个严格使用 MIN 和 MAX 的解决方案

这里的概念是将 MIN 应用于城市名称的 _长度_。在这些结果中，再次将 MIN 应用于城市名称 本身 以按字母顺序查找第一个。

当然，同样的方法使用 MAX 求最大城市长度，对两个查询执行一次 UNION 得到最终解。

没有排序， ASC ， DESC ， LIMIT 或 ORDER BY 需要。

很好的参考： https ://www.zentut.com/sql-tutorial/sql-min-max/

 (
    SELECT s3.CITY, s3.Len
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MIN(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
) UNION (
    SELECT s3.CITY, s3.Len
    FROM (
        SELECT CITY, s1.Len
        FROM (
            SELECT CITY, LENGTH(CITY) AS Len
            FROM STATION
        ) s1
        WHERE
            s1.Len = (
                SELECT MAX(s2.Len2)
                FROM (
                    SELECT LENGTH(CITY) AS Len2
                    FROM STATION
                ) s2
            )
        ) s3
    WHERE
        s3.CITY = (
            SELECT MIN(CITY)
            FROM STATION
            WHERE s3.Len = LENGTH(CITY)
        )
);

原文由 kmiklas 发布，翻译遵循 CC BY-SA 4.0 许可协议