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;