有没有一种方法可以合并这些查询？

Question

我开始在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。

这让我想到我的解决方案实际上可能是完成此任务最有效的方法，但我的集合思维始终需要加强，我不确定这是否适用于此处。

- mbm29414

---

使用联合体？或者这不是一个非平凡的事情吗？ - xQbert

也许我在问题措辞方面本身可以更清晰一些。;-) 我想我所问的是是否有任何方法可以避免编写2个完全独立且几乎平行的WHERE子句/条件。我还在思考这个要求可能足够复杂，不存在这样的方法，但我正在努力扩展我的知识，因此提出了这个问题。 - mbm29414

不使用TOP关键字，试试使用分析函数如何？ - xQbert

Answer 1

未经测试，但我看不出它不能工作的原因：

SELECT *
FROM (
    SELECT TOP (1) CITY, LEN(CITY) AS CITY_LEN
    FROM STATION
    ORDER BY CITY_LEN, CITY
    ) AS T
UNION ALL
SELECT *
FROM (
    SELECT TOP (1) CITY, LEN(CITY) AS CITY_LEN
    FROM STATION
    ORDER BY CITY_LEN DESC, CITY
    ) AS T2;

每个 SELECT 语句都不能使用带有 ORDER BY 的 UNION ALL，但您可以通过将子查询与 TOP (1) 子句和 ORDER BY 结合使用来解决此问题。

- Evaldas Buinauskas

Answer 2

这里有另一种选择。我认为它非常直观，易于理解正在发生的事情。性能很好。

但仍然有几个子查询。

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)

- jim31415

Answer 3

未经测试：

WITH CTE AS (
Select ID, len(City), row_number() over (order by City) as AlphaRN,
row_number() over (order by Len(City) desc) as LenRN) B
Select * from cte 
Where AlphaRN = 1 and (lenRN = (select max(lenRN) from cte) or 
                       lenRN = (Select min(LenRN) from cte))

- xQbert

---

我理解了整体的逻辑，看起来这个方案是可行的。B 是必要的吗？此外，虽然我欣赏你的创意解决方案（总是好的能够得到不同的视角！），但我认为你的 UNION 想法可能更加简洁和 less complex。 - mbm29414

Answer 4

这是我想出来的方法。我试图只使用一个查询，而不使用CTE或子查询。

;WITH STATION AS ( --Dummy table
SELECT *
FROM (VALUES
(1,'DEF','EU',1,9),
(2,'ABC','EU',1,6), -- This is shortest 
(3,'PQRS','EU',1,5),
(4,'WXY','EU',1,4),
(5,'FGHA','EU',1,2),
(6,'ASDFHG','EU',1,3) --This is longest 
) as t(ID, CITY, [STATE], LAT_N,LONG_W)
)


SELECT TOP 1 WITH TIES  CITY,
                        LEN(CITY) as CITY_LEN
FROM STATION
ORDER BY ROW_NUMBER() OVER(PARTITION BY LEN(CITY) ORDER BY LEN(CITY) ASC),
        CASE WHEN MAX(LEN(CITY)) OVER (ORDER BY (SELECT NULL)) = LEN(CITY) 
                OR MIN(LEN(CITY)) OVER (ORDER BY (SELECT NULL))= LEN(CITY) 
                        THEN 0 ELSE 1 END

输出：

CITY    CITY_LEN
ABC     3
ASDFHG  6

- gofr1

Answer 5

这是我能想到的最好的东西：

with Ordering as
(
    select
        City,
        Forward = row_number() over (order by len(City), City),
        Backward = row_number() over (order by len(City) desc, City)
    from
        Station
)
select City, len(City) from Ordering where 1 in (Forward, Backward);

从答案的多样性可以看出，有很多方法来解决这个问题，但我认为在表达预期行为方面，没有什么比原始的两次查询解决方案更清晰简洁了。不过这是一个有趣的问题！

- Joe Farrell

Answer 6

从STATION表中选择最小的城市名和城市名长度，按照城市名长度分组，筛选出城市名长度等于STATION表中最小城市名长度或最大城市名长度的记录。

- Aravindh V

---

这看起来与此答案完全相同：https://dev59.com/lJrga4cB1Zd3GeqPiiU-#39128307。 - user1781290