我开始在HackerRank上做一些编程问题,作为一种“有益的分心”。
我正在SQL部分的前几个问题上工作,遇到了这个问题(链接):
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。
这让我想到我的解决方案实际上可能是完成此任务最有效的方法,但我的集合思维始终需要加强,我不确定这是否适用于此处。