Welcome everyone

每周一练(6)

每周一练 汪明鑫 263浏览 0评论

某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。

Customers 表:

+—-+——-+
| Id | Name |
+—-+——-+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+—-+——-+

Orders 表:

+—-+————+
| Id | CustomerId |
+—-+————+
| 1 | 3 |
| 2 | 1 |
+—-+————+

例如给定上述表格,你的查询应返回:

+———–+
| Customers |
+———–+
| Henry |
| Max |
+———–+

 

 

 

answer1:


select name as Customers
from Customers 
where name not in
(select Name 
from Customers,Orders
WHERE Customers.id = Orders.CustomerId);

错误

 

错误case:

输入:

{“headers”: {“Customers”: [“Id”, “Name”], “Orders”: [“Id”, “CustomerId”]},
“rows”: {“Customers”: [[1, “James”], [2, “James”]], “Orders”: [[1, 1]]}}
输出
{“headers”: [“Customers”], “values”: []}
预期结果
{“headers”:[“Customers”],”values”:[[“James”]]}

 

同名是真的骚。。。
name不行就用id

 

select name as Customers
from Customers 
where Customers.id not in
(select Customers.id 
from Customers,Orders
WHERE Customers.id = Orders.CustomerId);

 

上面这个烂sql自己写的,有子循环,且效率不高

可以使用left join

select a.Name as Customers
from Customers as a
left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;

 

 

其他题解,推荐浏览!
https://leetcode-cn.com/problems/customers-who-never-order/solution/tu-jie-sqlmian-shi-ti-cha-zhao-bu-zai-biao-li-de-s/

 

转载请注明:汪明鑫的个人博客 » 每周一练(6)

喜欢 (0)

说点什么

您将是第一位评论人!

提醒
avatar
wpDiscuz