[LeetCode] 607. Sales Person_Easy tag: SQL

最新推荐文章于 2021-01-27 18:58:29 发布

转载最新推荐文章于 2021-01-27 18:58:29 发布 · 114 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/Johnsonxiong/p/9551037.html

博客围绕数据库查询展开，给定三个表，要求输出未向公司'RED'进行销售的销售人员姓名。通过分析表中的订单信息，可找出向'RED'公司有销售的人员，进而得到所需结果。

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

+----------+------+--------+-----------------+-----------+
| 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

+---------+--------+------------+
| 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

+----------+----------+---------+----------+--------+
| 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

+------+
| 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.

Code

naive

SELECT s.name FROM salesperson as s WHERE s.name NOT IN (SELECT DISTINCT s1.name FROM salesperson as s1 JOIN company as c JOIN orders as o WHERE s1.sales_id = o.sales_id AND c.name = 'RED' AND o.com_id = c.com_id)

Better

SELECT s.name FROM salesperson as s WHERE s.sales_id NOT IN (SELECT o.sales_id FROM orders as o LEFT JOIN company as c ON o.com_id = c.com_id
                                                            WHERE c.name = 'RED')

转载于:https://www.cnblogs.com/Johnsonxiong/p/9551037.html