首页 > itarticle > sql notes: leetcode#607 sales person

sql notes: leetcode#607 sales person

admin 11月 12, 2020 0

Problem

Description

Given three tables: salesperson, company, orders.
Output all the names in the table salesperson, who didn’t have sales to company ‘RED’.

Example
Input

Table: salesperson

1
2
3
4
5
6
7
8
9
 
+----------+------+--------+-----------------+-----------+
| sales_id | name | salary | commission_rate | hire_date |
+----------+------+--------+-----------------+-----------+
|   1      | John | 100000 |     6           | 4/1/2006  |
|   2      | Amy  | 120000 |     5           | 5/1/2010  |
|   3      | Mark | 65000  |     12          | 12/25/2008|
|   4      | Pam  | 25000  |     25          | 1/1/2005  |
|   5      | Alex | 50000  |     10          | 2/3/2007  |
+----------+------+--------+-----------------+-----------+

The table salesperson holds the salesperson information. Every salesperson has a sales_id and a name.
Table: company

1
2
3
4
5
6
7
8
 
+---------+--------+------------+
| com_id  |  name  |    city    |
+---------+--------+------------+
|   1     |  RED   |   Boston   |
|   2     | ORANGE |   New York |
|   3     | YELLOW |   Boston   |
|   4     | GREEN  |   Austin   |
+---------+--------+------------+

The table company holds the company information. Every company has a com_id and a name.
Table: orders

1
2
3
4
5
6
7
8
 
+----------+----------+---------+----------+--------+
| order_id |  date    | com_id  | sales_id | amount |
+----------+----------+---------+----------+--------+
| 1        | 1/1/2014 |    3    |    4     | 100000 |
| 2        | 2/1/2014 |    4    |    5     | 5000   |
| 3        | 3/1/2014 |    1    |    1     | 50000  |
| 4        | 4/1/2014 |    1    |    4     | 25000  |
+----------+----------+---------+----------+--------+

The table orders holds the sales record information, salesperson and customer company are represented by sales_id and com_id.
output

1
2
3
4
5
6
7
 
+------+
| name | 
+------+
| Amy  | 
| Mark | 
| Alex |
+------+

Explanation

According to order ‘3’ and ‘4’ in table orders, it is easy to tell only salesperson ‘John’ and ‘Alex’ have sales to company ‘RED’,
so we need to output all the other names in table salesperson.

Analysis

  • Join orders table and company table to find sales_id of who had sales to company ‘RED’:
1
2
3
 
SELECT o.sales_id FROM orders AS o 
JOIN company AS c ON o.com_id = c.com_id
WHERE c.name = 'RED';
  • Output names of salespersons whose sales_id is not in the list the above query return:
1
2
3
4
5
 
SELECT name FROM salesperson 
WHERE sales_id NOT IN 
(SELECT o.sales_id FROM orders AS o 
JOIN company AS c ON o.com_id = c.com_id
WHERE c.name = 'RED');

Solution

1
2
3
4
5
 
SELECT name FROM salesperson 
WHERE sales_id NOT IN 
(SELECT o.sales_id FROM orders AS o 
JOIN company AS c ON o.com_id = c.com_id
WHERE c.name = 'RED');

607. Sales Person
(中文版) SQL 笔记: Leetcode#607 Sales Person

微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能