- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用
目录
一,原题力扣链接
二,题干
表:
SalesPerson
+-----------------+---------+ | Column Name | Type | +-----------------+---------+ | sales_id | int | | name | varchar | | salary | int | | commission_rate | int | | hire_date | date | +-----------------+---------+ sales_id 是该表的主键列(具有唯一值的列)。 该表的每一行都显示了销售人员的姓名和 ID ,以及他们的工资、佣金率和雇佣日期。表:
Company
+-------------+---------+ | Column Name | Type | +-------------+---------+ | com_id | int | | name | varchar | | city | varchar | +-------------+---------+ com_id 是该表的主键列(具有唯一值的列)。 该表的每一行都表示公司的名称和 ID ,以及公司所在的城市。表:
Orders
+-------------+------+ | Column Name | Type | +-------------+------+ | order_id | int | | order_date | date | | com_id | int | | sales_id | int | | amount | int | +-------------+------+ order_id 是该表的主键列(具有唯一值的列)。 com_id 是 Company 表中 com_id 的外键(reference 列)。 sales_id 是来自销售员表 sales_id 的外键(reference 列)。 该表的每一行包含一个订单的信息。这包括公司的 ID 、销售人员的 ID 、订单日期和支付的金额。编写解决方案,找出没有任何与名为 “RED” 的公司相关的订单的所有销售人员的姓名。
以 任意顺序 返回结果表。
返回结果格式如下所示。
示例 1:
输入: SalesPerson 表: +----------+------+--------+-----------------+------------+ | sales_id | name | salary | commission_rate | hire_date | +----------+------+--------+-----------------+------------+ | 1 | John | 100000 | 6 | 4/1/2006 | | 2 | Amy | 12000 | 5 | 5/1/2010 | | 3 | Mark | 65000 | 12 | 12/25/2008 | | 4 | Pam | 25000 | 25 | 1/1/2005 | | 5 | Alex | 5000 | 10 | 2/3/2007 | +----------+------+--------+-----------------+------------+ Company 表: +--------+--------+----------+ | com_id | name | city | +--------+--------+----------+ | 1 | RED | Boston | | 2 | ORANGE | New York | | 3 | YELLOW | Boston | | 4 | GREEN | Austin | +--------+--------+----------+ Orders 表: +----------+------------+--------+----------+--------+ | order_id | order_date | com_id | sales_id | amount | +----------+------------+--------+----------+--------+ | 1 | 1/1/2014 | 3 | 4 | 10000 | | 2 | 2/1/2014 | 4 | 5 | 5000 | | 3 | 3/1/2014 | 1 | 1 | 50000 | | 4 | 4/1/2014 | 1 | 4 | 25000 | +----------+------------+--------+----------+--------+ 输出: +------+ | name | +------+ | Amy | | Mark | | Alex | +------+ 解释: 根据表orders
中的订单 '3' 和 '4' ,容易看出只有 'John' 和 'Pam' 两个销售员曾经向公司 'RED' 销售过。 所以我们需要输出表salesperson
中所有其他人的名字。
三,建表语句
Create table If Not Exists SalesPerson (sales_id int, name varchar(255), salary int, commission_rate int, hire_date date);
Create table If Not Exists Company (com_id int, name varchar(255), city varchar(255));
Create table If Not Exists Orders (order_id int, order_date date, com_id int, sales_id int, amount int);
Truncate table SalesPerson;
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('1', 'John', '100000', '6', '2006-4-1');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('2', 'Amy', '12000', '5', '2010-05-01');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('3', 'Mark', '65000', '12', '2008-12-25');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('4', 'Pam', '25000', '25', '2005-01-01');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('5', 'Alex', '5000', '10', '2007-03-02');
insert into SalesPerson (sales_id, name, salary, commission_rate, hire_date) values ('6', 'Alex11', '5000', '10', '2007-03-02');
Truncate table Company;
insert into Company (com_id, name, city) values ('1', 'RED', 'Boston');
insert into Company (com_id, name, city) values ('2', 'ORANGE', 'New York');
insert into Company (com_id, name, city) values ('3', 'YELLOW', 'Boston');
insert into Company (com_id, name, city) values ('4', 'GREEN', 'Austin');
insert into Company (com_id, name, city) values ('6', 'xx,RED,GREEN', 'Austin11');
Truncate table Orders;
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('1', '2014-01-01', '3', '4', '10000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('2', '2014-01-02', '4', '5', '5000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('3', '2014-01-03', '1', '1', '50000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('4', '2014-01-04', '1', '4', '25000');
insert into Orders (order_id, order_date, com_id, sales_id, amount) values ('5', '2014-01-04', '6', '6', '25000');
select * from salesperson;
select * from company;
select * from Orders;
四,分析
题解:
第一张表:销售表
字段:销售id ,销售员,佣金,入职日期
第二张表:公司表
字段:公司id,公司名称,公司城市
第三张表:订单表
字典:订单id,订单日期,公司名称,销售员名称,销量
题目:找到没有任何与RED公司相关的订单 的 所有销售员的姓名;
思路:
第一步,查询公司表 ,找到是RED的公司id
第二步,查询订单订单表,找到和RED公司id是一样的 销售员id
第三步,查询销售表,找到和red公司 无关的销售员id not in
思路二,邪道解法 不严谨
使用group_concat 然后分组 求
连接三个表之后,然后分组 合并表里面的列,最后从列里面筛选 不是red的 这个解法不严谨。
五,SQL解答
-- 严谨的解法
-- 拿到是red的公司id
select com_id as cid from company where name ='RED';
-- 根据 RED的id 拿到 订单表 和red有业务往来的 销售员id
select sales_id from orders where com_id = (select com_id as cid from company where name ='RED');
-- 查询销售表 id 不是和red有关系的人 拿到姓名
select name from salesperson where sales_id not in
(select sales_id from orders where com_id = (select com_id as cid from company where name ='RED'));
六,验证
正规严谨的解法:
不严谨的方法 通过了。。。。
七,知识点总结
- 子查询练习;
- 多表查询的时候;
- 根据条件取对应的其他列;
- not in 的连续运用
- 学习:知识的初次邂逅
- 复习:知识的温故知新
练习:知识的实践应用