Leetcode-SQL学习计划-SQL入门-584：寻找用户推荐人

 

 

建表语句：

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')

答案：

select name
from customer
where 
    ifnull(referee_id,0)<>2

或

select name 
from customer
where id  not in 
(select id 
from customer where  referee_id =2)

或：包含空值的不等于<=>，覆盖空值

SELECT name FROM customer WHERE not referee_Id <=> 2;

或：or

# Write your MySQL query statement below
select name
from customer
where 
    referee_id is null
or 
    referee_id!=2

或：union all

union会去除重复的行,当用户name相同但id不同的时候,用union会丢失结果.

换成union all即可

SELECT name
FROM customer
WHERE referee_id IS NULL

UNION ALL

SELECT name
FROm customer
WHERE referee_id != 2