建表语句:
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