iT邦幫忙

0

在leetcode上的SQL刷題記錄

繼之前在hackerrank上的SQL刷題記錄的分享,
hackkerrank上的題目刷的差不多了,換leetcode上的問題試試

leetcode上有一部分的題目是專門用來練習sql的,
在leetcode的題庫找到database這個類別

https://ithelp.ithome.com.tw/upload/images/20200802/20117114NeIt9RVbnF.png

開始之前,若語法想不出來可參考這個:
查詢語法的網站: SQL語法教學

外部連結- outer join語法

題目: 175. Combine Two Tables
資料表名稱一: Person
欄位: PersonId、FirstName、LastName

資料表名稱二: Address
欄位: AddressId、PersonId、City、State

要求: 對於資料表Person的每個人,PersonId去Address資料表查詢,輸出每個人的FirstName, LastName, City, State(若Address資料表查不到PersonId,City, State就輸出空值)

思路: 一開始會有點想直接用where條件篩選

select t1.FirstName, t1.LastName, t2.city, t2.state
from person t1, address t2
where t1.PersonId = t2.PersonId

但這個解法不行,如果Address資料表查不到Person的PersonId,
資料就被過濾掉了

需要用outer join的語法,
與「inner join」相比,
inner join會過濾不合條件的資料,
outer join會保留資料

這邊不同的資料庫伺服器的語法略有不同,
MySQL的語法是from...left join...on

select t1.FirstName, t1.LastName, t2.city, t2.state
from person t1 left join address t2
on t1.PersonId = t2.PersonId

Oracle的語法直接在原本的where t1.PersonId = t2.PersonId條件後寫個(+)即可

select t1.FirstName, t1.LastName, t2.city, t2.state
from person t1, address t2
where t1.PersonId = t2.PersonId(+)

order by- 排序, limit- 限制取出幾筆資料, offset- 跳過幾筆資料

題目: 176. Second Highest Salary
資料表名稱: Employee
欄位: Id、Salary

要求: 回傳第二高的salary,若沒有則返回null

思路: 將資料按遞減順序排,取出1筆資料、跳過1筆資料,就是第二高的salary了
(參考: [筆記] MYSQL/PostgreSQL語法中的LIMIT、OFFSET用法範例說明)

其實蠻想要這樣寫的:

select distinct Salary as SecondHighestSalary
from Employee
order by Salary desc limit 1 offset 1

但是解答說沒有資料時要返回null,
上述寫法則不會返回資料,
還需要再包一層select

select (select distinct Salary
from Employee
order by Salary desc limit 1 offset 1)
as SecondHighestSalary

自己看到也有點震驚,這樣寫真的有差嗎?
不過先筆記起來,有一天也許會懂

原來SQL也有自訂函數

題目: 177. Nth Highest Salary
資料表名稱: Employee
欄位: Id、Salary

要求: 定義一個函數,回傳第n高的salary,若沒有則返回null
思路: 其實這一題跟「Second Highest Salary」相當相似,
只是第一次看到說SQL也能自訂函數,格式如下:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      # Write your MySQL query statement below.
      
  );
END

我想說這不是跟上一題一樣,將資料按遞減順序排,取出1筆資料、跳過「n-1」筆資料就好嗎?
我嘗試這樣寫:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  RETURN (
      select
      (select distinct Salary
       from Employee
       order by Salary desc limit 1 offset n-1)
  );
END

然而直接在offset那邊打n-1程式編譯不過,
看了討論區參考解答才知要多一行set n = n-1;
底下這樣寫就ok了:

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  set n  = n-1;
  RETURN (
      select
      (select distinct Salary
       from Employee
       order by Salary desc limit 1 offset n)
  );
END

Rank, Dense_Rank取得排序後的名次

題目: 178. Rank Scores
資料表名稱: Scores
欄位: Id、Score

要求: 按分數排序輸出名次,
範例:

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

要輸出

+-------+---------+
| score | Rank    |
+-------+---------+
| 4.00  | 1       |
| 4.00  | 1       |
| 3.85  | 2       |
| 3.65  | 3       |
| 3.65  | 3       |
| 3.50  | 4       |
+-------+---------+

可參考: Rank、Dense_Rank、Row_Number用法與範例
解法:

select score, dense_rank() over (order by score desc) as `Rank`
from Scores

Rank、Dense_Rank的差別在於同名之後要不要跳過一個名次
這邊另有一個重點,Rank是SQL內的函數名稱,
如果要用Rank為欄位命名需用特殊符號「`Rank`」

group by- 相同資料合併、雙重select用法

題目: 182. Duplicate Emails
資料表名稱: Person

找出所有重複的email
範例:

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

要輸出
+---------+
| Email   |
+---------+
| a@b.com |
+---------+
(因為a@b.com出現兩次)

這裡可以用group by計算出每個email的出現次數,
參考: SQL Group By

舉例來說,使用

select Email, count(Email) as num
from Person
group by Email

會輸出

| Email   | num |
|---------|-----|
| a@b.com | 2   |
| c@d.com | 1   |

搭配「雙重select用法」的方法(不確定是不是有專有名詞),從這張表找num>1的值就是了

select t.Email from
    (select Email, count(Email) as num
    from Person
    group by Email) as t
where num > 1

需注意在「雙重select用法」必須幫裡面那層資料表取「別名」(as語法)

子查詢

題目: 183. Customers Who Never Order

題意: 有CustomersOrders兩張資料表,找出沒有在出現在Orders裡的顧名名字
範例:

(資料表 Customers)
+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

(資料表 Orders)
+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

要回傳
+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

子查詢的語法可參考: SQL 子查詢
在where條件去另一個資料表中查詢

select name as Customers
from Customers t1
WHERE t1.Id not in
(SELECT CustomerId FROM Orders)

修改資料表內的資料

上述題目清一色都是「如何從資料表中選取我們要的資料」,
那如果是真的要修改資料表中的資料怎麼辦呢?

delete from- 刪除資料

delete from 的基礎語法可參考SQL Delete From

題目: 196. Delete Duplicate Emails
要求: 刪除資料表中重複的email(優先保留id小的)
範例:

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

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

先思考一下,如果單純用select來寫,
要刪掉的對象就是有重複email而且id比較大的

select p1.*
from Person p1, Person p2
where p1.Email = p2.Email 
and p1.Id > p2.Id

將select直接以delete改寫就會刪除資料了

delete p1.*
from Person p1, Person p2
where p1.Email = p2.Email 
and p1.Id > p2.Id

update set - 更新資料、 when then - 類似一般程式的if else

update的基礎語法可參考SQL UPDATE

題目: 627. Swap Salary
要求: 將資料表中的sex欄位,若原本是m改f,若原本是f改m
範例:

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

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

程式:

update salary 
set sex= 
    case sex
        when 'm' then 'f'
        when 'f' then 'm'
    end

insert into

insert into是將資料插入資料庫的語法,
目前尚未刷到相關題目,
姑且附上語法參考連結:SQL Insert Into

心得: 還好leetcode上大部分的題目都鎖住了,
用了一天的時間大致上把能做的題目做完,
也學到蠻多特別的SQL語法,
不過SQL語法跟一般程式語言蠻不同的,
還不太習慣就是了


尚未有邦友留言

立即登入留言