HackerRank天气观测站5

Question

我想知道为什么我的代码不起作用。这个问题曾经在这里被问过：Query the two cities in STATION with the shortest and longest CITY names,

这里的解决方案：https://github.com/chhayac/SQL-hackerrank-problems/blob/master/basic-select.md

但这两种答案都行不通。我把下面的问题贴在下面，然后是我的解决方案。谢谢你的帮助！

使用最短和最长的城市名称以及它们各自的长度(即:名称中的字符数)查询车站中的两个城市。如果有多个最小或最大的城市，请按字母顺序选择最优先的城市。

输入格式

所述车站表如下：

Station.jpg

LAT_N是北纬，LONG_W是西经。

样本输入

假设这个城市只有四个条目: DEF，ABC，PQRS和WXY

样本输出

ABC 3
PQRS 4

解释

当按字母顺序排列时，城市名称被列出为ABC、DEF、PQRS和WXY，并有各自的长度和。命名最长的城市显然是PQRS，但也有最短命名城市的选择；我们选择ABC，因为它以字母顺序排在首位。

注意，您可以编写两个单独的查询来获得所需的输出。它不需要一个查询。

我的答案是：

/shortest字符长度按字母顺序排序/

SELECT city, LENGTH(city) as length_char
FROM station
ORDER BY LENGTH(city) ASC, city ASC
LIMIT 1;

/longest字符长度按字母顺序排序/

SELECT city, LENGTH(city) as length_char
FROM station
ORDER BY LENGTH(city) DESC
LIMIT 1;

Answer 1

您在github上的解决方案如下：

select city, length(city) from station order by length(city) DESC,city ASC fetch first row only;
select city, length(city) from station order by length(city) asc ,city asc fetch first row only;  

这里有一个问题-没有像fetch first row only这样的命令。根据数据库系统的不同，它可以是top、limit或rownum -请在这里阅读更多信息- top.asp。

因此，根据系统的不同，答案也是不同的。

甲骨文

select * from (select city c, length(city) l
from   station
order by l desc, c asc)
where rownum = 1;

select * from (select city c, length(city) l
from   station
order by l asc, c asc)
where rownum = 1;

Server

select top 1 city c, len(city) l
from   station
order by l desc, c asc;

select top 1 city c, len(city) l
from   station
order by l asc, c asc;

MySQL

select city c, length(city) l
from   station
order by l desc, c asc
limit 1;

select city c, length(city) l
from   station
order by l asc, c asc
limit 1;

或者使用union

(select city, length(city) 
from station 
order by length(city) asc , city asc limit 1) 
union
(select city,length(city) 
from station 
order by length(city) desc, city asc limit 1)

Answer 2

SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY ASC LIMIT 1;

SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) ASC,CITY ASC LIMIT 1; 

/* FOR MYSQL */

Answer 3

select t2.city , t2.t  
from  
(  
    select t1.city , t1.t , row_number() over (partition by t1.t order by t1.city) as ro 
    from
        ( select city , length(city)as t 
          from station 
        ) t1
    group by t1.city,t1.t
    having 
        t1.t = (select min(length(city)) from station )
                           or 
        t1.t = (select max(length(city)) from station)
) t2
where t2.ro = 1    ;    

表t2将提供所有具有最小和最大字符串长度的记录，以及行编号，现在，基于行num的过滤记录将获取所需的输出。