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

Leetcode-SQL学习计划-SQL入门-584：寻找用户推荐人的相关教程结束。