SQL基礎查詢語法 顧客的推薦人_Find Customer Referee_Leetcode #584

Cytonn Photography on Unsplash

題目敘述

題目會給我們一張Customer資料表，裡面分別有id、name、referee_id 等欄位，其中id 是主鍵Primary Key。

+-------------+---------+

| Column Name | Type    |

+-------------+---------+

| id          | int     |

| name        | varchar |

| referee_id  | int     |

+-------------+---------+

In SQL, id is the primary key column for this table.

Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them.

詳細的題目可在這裡看到

測試範例

Example 1:

Input: 
Customer table:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+
Output: 
+------+
| name |
+------+
| Will |
| Jane |
| Bill |
| Zack |
+------+

約束條件

列出所有推薦人ID referee_id不等於2的顧客，印出順序不拘。

演算法

入門題，複習基礎的SELECT ...欄位 FROM ...表格 WHERE ...條件 SQL查詢語法

依序填入即可，實作上的細節要留意，推薦人ID referee_id可能為NULL，這時候可以用

... IS NULL的SQL語法來判斷是否為空值NULL

如果是第一次接觸SQL的同學，請到這邊學習基本的SQL 語法。

程式碼

SELECT name

FROM Customer

WHERE referee_id IS NULL OR referee_id  <> 2;

關鍵知識點

掌握基本的SELECT ...欄位 FROM ...表格 WHERE ...條件 和 空值判斷 ... IS NULL 的SQL查詢語法即可

Reference:

[1]MySQL by SELECT .... FROM ... WHERE ... - Find Customer Referee - LeetCode