leetCode資料查詢筆記（簡單）

innate發表於2018-07-26
LeetCode筆記

說明

LeetCode上的資料庫題，涉及到的大多是單表操作，資料量也不大，所以可以作為熟悉SQL這種集合式程式語言的練習。

點選題目可以直接跳轉

595. 大的國家

這裡有張World表

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

如果一個國家的面積超過300萬平方公里，或者人口超過2500萬，那麼這個國家就是大國家。

編寫一個SQL查詢，輸出表中所有大國家的名稱、人口和地區。

例如，根據上表，我們應該輸出:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

思路/筆記：這道題太基礎了，上面的贊和踩比例是2：8。和指令式程式設計語言、函數語言程式設計語言不同，SQL太像日常使用的英語了。

Fast Lane裡面有一句歌詞：

CATCH me 
   IN my mercedes

和

SELECT fish
  FROM river

就是一樣的格式——但SQL和自然語言又不同，用SQL寫程式碼就需要轉換為SQL的思維方式。

select name, population, area
  from World
 where population > 25000000
    or area > 3000000

182. 查詢重複的電子郵箱

編寫一個 SQL 查詢，查詢 Person 表中所有重複的電子郵箱。

示例：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

根據以上輸入，你的查詢應返回以下結果：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

說明：所有電子郵箱都是小寫字母。
思路/筆記：這道題也屬於基礎，where和having的區別是：

  • where作用於單條記錄
  • having可以作用於所有記錄聚合的結果，比如count，sum，max，avg。

where就像把一塊蛋糕切出想要的部分；having就像

select Email
  from Person
 group by Email
having count(0) > 1

627. 交換工資

給定一個 salary表，如下所示，有m=男性 和 f=女性的值 。交換所有的 f 和 m 值(例如，將所有 f 值更改為 m，反之亦然)。要求使用一個更新查詢，並且沒有中間臨時表。

例如:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

執行你所編寫的查詢語句之後，將會得到以下表:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

思路/筆記：基礎題，CASE是一個很強大的功能，很多條件判斷都可以用CASE替代WHERE，而窮更加方便。

update salary
   set sex = (CASE sex WHEN `m` THEN `f`
                       WHEN `f` THEN `m`
              ELSE sex END)

620. 有趣的電影

某城市開了一家新的電影院，吸引了很多人過來看電影。該電影院特別注意使用者體驗，專門有個 LED顯示板做電影推薦，上面公佈著影評和相關電影描述。

作為該電影院的資訊部主管，您需要編寫一個 SQL查詢，找出所有影片描述為非 boring (不無聊) 的並且 id 為奇數 的影片，結果請按等級 rating 排列。

例如，下表 cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+

對於上面的例子，則正確的輸出是為：

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

思路/筆記：基礎題

select id, movie, description, rating
  from cinema
 where description <> `boring`
   and (id%2) = 1
 order by rating desc

175. 組合兩個表

表1: Person

+-------------+---------+
| 列名         | 型別     |
+-------------+---------+
| PersonId    | int     |
| FirstName   | varchar |
| LastName    | varchar |
+-------------+---------+

PersonId 是上表主鍵
表2: Address

+-------------+---------+
| 列名         | 型別    |
+-------------+---------+
| AddressId   | int     |
| PersonId    | int     |
| City        | varchar |
| State       | varchar |
+-------------+---------+

AddressId 是上表主鍵

編寫一個 SQL 查詢，滿足條件：無論 person 是否有地址資訊，都需要基於上述兩表提供 person 的以下資訊：

FirstName, LastName, City, State

思路/筆記：基礎題，一般來說JOIN都要少用，如果有A、B兩表，資料量分別為m、n。那麼join的運算量就是m×n，如果再加一張資料量為l的C表，就是m×n×l，時間複雜度為O(n^3)。所以最好能夠通過好的表結構設計，避免大表的JOIN運算。

另外有一些很長的字串，比如公司編號、商店編號、收銀裝置編號、會員卡號等，在每一筆交易裡都存放的話，會佔據更多的空間，可以通過建立對映表的方式存放，使用INT/NUMBER等存放，既節省了空間，又可以提高計算效率。

select p.FirstName, p.LastName, a.City, a.State
  from Person p 
  left join Address a on p.PersonId = a.PersonId

181. 超過經理收入的員工

Employee 表包含所有員工，他們的經理也屬於員工。每個員工都有一個 Id，此外還有一列對應員工的經理的 Id。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

給定 Employee 表，編寫一個 SQL 查詢，該查詢可以獲取收入超過他們經理的員工的姓名。在上面的表格中，Joe 是唯一一個收入超過他的經理的員工。

+----------+
| Employee |
+----------+
| Joe      |
+----------+

思路/筆記：基礎題，關於這種樹狀結構表的遞迴查詢有一個語句：select…start with…connect by…prior

select e1.Name Employee
  from Employee e1,Employee e2
 where e1.ManagerId = e2.Id
   and e1.Salary > e2.Salary

183. 從不訂購的客戶

某網站包含兩個表，Customers表和Orders表。編寫一個 SQL 查詢，找出所有從不訂購任何東西的客戶。

Customers 表：

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders 表：

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

例如給定上述表格，你的查詢應返回：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

思路/筆記：基礎題，exists和in的區別

select c.Name Customers
  from Customers c
 where not exists(select 1
                    from Orders o
                   where c.Id = o.CustomerId)

196. 刪除重複的電子郵箱

編寫一個 SQL 查詢，來刪除 Person 表中所有重複的電子郵箱，重複的郵箱裡只保留 Id 最小 的那個。

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

Id 是這個表的主鍵。
例如，在執行你的查詢語句之後，上面的 Person 表應返回以下幾行:

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

思路/筆記：自連線可以用來解決很多同表內資料的比較，這裡要注意的是p3不可少，否則會出現You can`t specify target table `Person` for update in FROM clause錯誤。

  delete
    from Person
   where Id in (select Id
                  from (select p2.Id
                          from Person p1, Person p2
                         where p1.Email = p2.Email
                           and p1.Id < p2.Id)p3)

197. 上升的溫度

定一個 Weather 表，編寫一個 SQL 查詢，來查詢與之前（昨天的）日期相比溫度更高的所有日期的 Id。

+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
|       1 |       2015-01-01 |               10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+---------+------------------+------------------+

例如，根據上述給定的 Weather 表格，返回如下 Id:

+----+
| Id |
+----+
|  2 |
|  4 |
+----+

思路/筆記：用自連線的方式，可以找到相鄰資料，在Oracle裡有LAG（），LEAD（）函式可以用，但通用的還是自連線。

select w2.Id
  from Weather w1, Weather w2
 where (datediff(w1.RecordDate, w2.RecordDate) = -1)
   and w1.Temperature < w2.Temperature

596. 超過5名學生的課

有一個courses 表 ，有: student (學生) 和 class (課程)。

請列出所有超過或等於5名學生的課。

例如,表:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

應該輸出:

+---------+
| class   |
+---------+
| Math    |
+---------+

Note:
學生在每個課中不應被重複計算。
思路/筆記：不能有重複的，所以需要加distinct

select class
  from (select distinct student, class
          from courses) c
 group by class
having count(0) >= 5

176. 第二高的薪水

編寫一個 SQL 查詢，獲取 Employee 表中第二高的薪水（Salary） 。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查詢應該返回 200 作為第二高的薪水。如果不存在第二高的薪水，那麼查詢應返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

思路/筆記：mysql裡沒有rownum這個值，所以可以通過@rownum賦值，不過mysql裡可以直接用select 1這種形式，oracle裡則需要select 1 from dual

# select (case Salary when Salary then Salary
#         else `` end) SecondHighestSalary
#   from (select e.Salary, @rownum:=@rownum+1 rownum
#           from Employee e, (select @rownum:=0) r
#          order by e.Salary desc)s
#  where rownum = 2

SELECT
    (SELECT DISTINCT Salary
       FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET 1) AS SecondHighestSalary

相關文章