584. Find Customer Referee

題目敘述

SQL Schema >

Create table If Not Exists Customer (id int, name varchar(25), referee_id int)
Truncate table Customer
insert into Customer (id, name, referee_id) values ('1', 'Will', 'None')
insert into Customer (id, name, referee_id) values ('2', 'Jane', 'None')
insert into Customer (id, name, referee_id) values ('3', 'Alex', '2')
insert into Customer (id, name, referee_id) values ('4', 'Bill', 'None')
insert into Customer (id, name, referee_id) values ('5', 'Zack', '1')
insert into Customer (id, name, referee_id) values ('6', 'Mark', '2')

Table: Customer

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
| referee_id  | int     |
+-------------+---------+
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.

Write an SQL query to report the names of the customer that are not referred by the customer with id = 2.

Return the result table in any order.

The query result format is in the following example.

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 |
+------+

題目翻譯

現在有一張資料表 Customer，編寫一個 SQL Query 在不要求排序的情況下找出，沒有連接到 id = 2 的 customer。

解法解析

基本上就是簡單的用 WHERE 關鍵字來篩選即可，需要注意的就是其中 referee_id 是有 NULL 的值，如果只單純用 <> 2 或 != 2 會沒有辦法比對出來。所以需要再加上 IS NULL 的條件。

解法範例

SQL

# Write your MySQL query statement below
SELECT name
FROM `Customer`
WHERE referee_id <> 2
    OR referee_id IS NULL;

LeetCode Solution, Easy, 584. Find Customer Referee

Comments

LeetCode Solution

LeetCode Solution, Medium, 198. House Robber

More from this blog

如何開始入門軟體工程領域 - 名詞解釋（長期更新）

我的 MacBook Pro (Apple Silicon) 設定

ChatGPT 下的發展預想

一些覺得有用的工具蒐藏

LeetCode Solution, Medium, 607. Sales Person

584. Find Customer Referee

題目敘述

題目翻譯

解法解析

解法範例

SQL

Command Palette

Comments

LeetCode Solution

LeetCode Solution, Medium, 198. House Robber

More from this blog

584. Find Customer Referee

題目敘述

題目翻譯

解法解析

解法範例

SQL