1.問題：Query a list of?CITY?names from?STATION?with even?ID?numbers only. You may print the results in any order, but must exclude duplicates from your answer.

答案：SELECT DISTINCT city FROM station WHERE MOD(id,2)=0;

解析：要輸出偶數(shù)id的城市名稱且不能出現(xiàn)重復，distinct是去重，mod求余函數(shù)

2.Let N?be the number of?CITY?entries in?STATION, and let N`?be the number of distinct?CITY?names in?STATION; query the value of? N-N`?from?STATION. In other words, find the difference between the total number of?CITY?entries in the table and the number of distinct?CITY?entries in the table.

答案：SELECT COUNT(city)-COUNT(DISTINCT city) FROM station;

解析：通過統(tǒng)計函數(shù)進行數(shù)據(jù)統(tǒng)計并進行運算。

3.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.

答案：

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

解析：首先查詢城市名字，城市長度通過長度排序，升序排序找到最短名字，使用limit來返回第一行數(shù)據(jù)；

4.Query the list of?CITY?names starting with vowels (i.e.,?a,?e,?i,?o, or?u) from?STATION. Your result?cannot?contain duplicates.

答案：

SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(SUBSTR(CITY,1,1)) in('a','e','i','o','u') ;

解析：因為是范圍選擇所以選擇in，不能重復用distinct，截取城市名字的第一個字母并轉(zhuǎn)成小寫。

5.Query the list of?CITY?names ending with vowels (a, e, i, o, u) from?STATION. Your result?cannot?contain duplicates.

答案：

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '[aeiou]$';

解析：regexp是mysql中的正則表達式，表示匹配到aeiou就提出來，其中有幾個定位符：“.”代表任意字符；在中括號外：“^”代表文本開始；“$”文本結(jié)束

查詢首字母及最后一個字母為（aeiou）時將其變成^[aeiou].*[aeiou]$

查詢除了這些字母外，其他作為首字母的城市名字變成^[^aeiou]：中括號內(nèi)的^表示除了這些之外

6.Query the list of?CITY?names from?STATION?that?do not end?with vowels. Your result cannot contain duplicates.

答案：select distinct city from station where city not regexp '[aeiou]$';

分析：用not將最后為元音的都排除掉。

?

轉(zhuǎn)載于:https://www.cnblogs.com/gnn0426/p/9672274.html

總結(jié)

以上是生活随笔為你收集整理的hackerrank刷题的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。