LeetCode：Customers Who Never Order - 从未下单的消费者

2019独角兽企业重金招聘Python工程师标准>>>

1、题目名称

Customers Who Never Order（从未下单的消费者）

2、题目地址

https://leetcode.com/problems/customers-who-never-order/

3、题目内容

一个网站有两张表，表Customers和表Orders，写一个SQL找到所有未购买任何物品的顾客。

表Customers结构如下：

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

表Orders结构如下：

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

以上面两张表为例，查询的结果集应为：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

4、初始化数据库脚本

在MySQL数据库中建立一个名为LEETCODE的数据库，用MySQL命令行中的source命令执行下面脚本：

-- 执行脚本前必须建立名为LEETCODE的DATABASE
USE LEETCODE;
   
DROP TABLE IF EXISTS Customers;
CREATE TABLE Customers (
  Id INT NOT NULL PRIMARY KEY,
  Name VARCHAR(50)
);

INSERT INTO Customers (Id, Name) VALUES (1, 'Joe');
INSERT INTO Customers (Id, Name) VALUES (2, 'Henry');
INSERT INTO Customers (Id, Name) VALUES (3, 'Sam');
INSERT INTO Customers (Id, Name) VALUES (4, 'Max');

DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
  Id INT NOT NULL PRIMARY KEY,
  CustomerId INT
);

INSERT INTO Orders (Id, CustomerId) VALUES (1, 3);
INSERT INTO Orders (Id, CustomerId) VALUES (2, 1);

5、解题SQL1 - 使用 NOT IN

SELECT Name
FROM   Customers
WHERE  id NOT IN (SELECT CustomerId
                  FROM   Orders);

6、解题SQL2 - 使用 LEFT JOIN

SELECT Name AS Customers
FROM   Customers
LEFT   JOIN Orders ON Customers.Id = Orders.CustomerId
WHERE  Orders.CustomerId IS NULL;

7、解题SQL3 - 使用 NOT EXISTS

SELECT Name
FROM   Customers AS Cst
WHERE  NOT EXISTS (SELECT Ord.CustomerId
        FROM   Orders Ord
        WHERE  Cst.Id = Ord.CustomerId);

END

转载于:https://my.oschina.net/Tsybius2014/blog/648260