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

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

建表语句：

Create table If Not Exists Customer (id int, name varchar(25), referee_id int)Truncate table Customerinsert 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 namefrom customerwhere ifnull(referee_id,0)<>2

或

select name from customerwhere 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 belowselect namefrom customerwhere referee_id is nullor referee_id!=2

或：union all

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

换成union all即可

SELECT nameFROM customerWHERE referee_id IS NULLUNION ALLSELECT nameFROm customerWHERE referee_id != 2

作者：​​哥们要飞​​​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。