【题目描述】
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders
表:
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
例如给定上述表格,你的查询应返回:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
【题目解答】
首先建表、生成数据
drop table if exists customers;
drop table if exists orders;
create table `customers` (
`id` int(11) not null auto_increment,
`name` char(20) not null,
primary key(`id`)
)engine=innodb charset=utf8;
create table `orders` (
`id` int(11) not null auto_increment,
`customerid` char(20) not null,
primary key(`id`)
)engine=innodb charset=utf8;
insert into customers(name) value("joe"),("henry"),("sam"),("max");
insert into orders(customerid) value(3),(1);
【题目解答】
mysql> select name as Customers from customers where id not in (select customeri
d from orders);
+-----------+
| Customers |
+-----------+
| henry |
| max |
+-----------+
2 rows in set (0.00 sec)