sql - 查询长度最长和最短的城市名称

Question

我编写了一个查询来返回 MS SQL SERVER 数据库中长度最短和最长的城市。

Select city, len(city) as l 
From Station Where len(city) in 
((select max(len(city)) from station)
Union
(select min(len(city)) from station)) 
Order by l,city;

我的困难是我得到了重复，因为我有几个城市的长度最长和最短。此外，当我尝试ORDER BY CITY在两个子查询中失败时。

有什么建议吗？

标签： sqlsql-server-2008subqueryunion

Answer 1

我可能会这样做：

select top (1) with ties city
from station
order by len(city) asc
union 
select top (1) with ties city
from station
order by len(city) desc;

或者：

select distinct city
from station
where len(city) = (select max(len(city)) from station) or
      len(city) = (select min(len(city)) from station);