sql-server - 有没有办法组合这些查询？

Question

我已经开始在 HackerRank 上解决一些编程问题，作为一种“有效的消遣”。

我在处理 SQL 部分的前几个问题时遇到了这个问题 (link):

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.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is 
the western longitude.

Sample Input

Let's say that CITY only has four entries: 
1. DEF
2. ABC
3. PQRS
4. WXY

Sample Output

ABC 3
PQRS 4

Explanation

When ordered alphabetically, the CITY names are listed 
as ABC, DEF, PQRS, and WXY, with the respective lengths
3, 3, 4 and 3. The longest-named city is obviously PQRS, 
but there are  options for shortest-named city; we choose 
ABC, because it comes first alphabetically.

我同意这个要求可以写得更清楚，但基本要点很容易理解，尤其是在澄清示例的情况下。不过，我想到了这个问题，因为该问题的评论中给出的说明如下:

/*
Enter your query here.
Please append a semicolon ";" at the end of the query and 
enter your query in a single line to avoid error.
*/

现在，在一行中写一个查询并不一定意味着一个查询，尽管这似乎是该语句的预期主旨。但是，我能够使用以下提交(提交两行，中间有一个回车符)通过测试用例:

SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY), CITY;
SELECT TOP 1 CITY, LEN(CITY) FROM STATION ORDER BY LEN(CITY) DESC, CITY;

同样，这些都不是高级 SQL。但这让我开始思考。是否有一种非常重要的方法可以将此输出组合成一个结果集？我有一些想法，其中 WHERE 子句基本上在 OR 语句中添加一些子查询以将两个查询合并为一个。这是我通过测试用例的另一个提交:

SELECT 
    CITY, 
    LEN(CITY) 
FROM 
    STATION 
WHERE 
    ID IN (SELECT TOP 1 ID FROM STATION ORDER BY LEN(CITY), CITY) 
OR 
    ID IN (SELECT TOP 1 ID FROM STATION ORDER BY LEN(CITY) DESC, CITY)
ORDER BY 
    LEN(CITY), CITY;

而且，是的，我意识到最后一个 ORDER BY 子句中的最后一个 , CITY 是多余的，但它有点说明这个查询没有确实节省了那么多精力，尤其是在单独返回查询结果方面。

注意:这不是真正的 MAX 和 MIN 情况。给定以下输入，您实际上并没有获取第一行和最后一行:

Sample Input
1. ABC
2. ABCD
3. ZYXW

根据书面要求，您将选择#1 和#2，而不是#1 和#3。

这让我觉得我的解决方案实际上可能是实现这一目标的最有效方法，但我的基于集合的思维总是需要一些强化，我不确定这是否可行在这里与否。

Answer 1

这是另一种选择。我认为它非常简单明了，很容易理解发生了什么。性能良好。

不过还有几个子查询。

select 
   min(City), len(City)
from Station 
group by 
   len(City)
having 
   len(City) = (select min(len(City)) from Station)
   or 
   len(City) = (select max(len(City)) from Station)