181. Employees Earning More Than Their Managers

于 2021-07-15 17:20:31 发布
阅读量98 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SDS_19/article/details/118755664
版权

 Problem: Given the Employee table, write a SQL query that finds out employees who earn more than their managers.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Solution

内连接：自连接

表连接的本质就是笛卡尔积现象，显示所有记录的乘积

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from dept a,dept b;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME      | LOC      | DEPTNO | DNAME      | LOC      |
+--------+------------+----------+--------+------------+----------+
|     40 | OPERATIONS | BOSTON   |     10 | ACCOUNTING | NEW YORK |
|     30 | SALES      | CHICAGO  |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |     10 | ACCOUNTING | NEW YORK |
|     10 | ACCOUNTING | NEW YORK |     10 | ACCOUNTING | NEW YORK |
|     40 | OPERATIONS | BOSTON   |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |     20 | RESEARCH   | DALLAS   |
|     20 | RESEARCH   | DALLAS   |     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |     30 | SALES      | CHICAGO  |
|     30 | SALES      | CHICAGO  |     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |     30 | SALES      | CHICAGO  |
|     10 | ACCOUNTING | NEW YORK |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |     40 | OPERATIONS | BOSTON   |
|     20 | RESEARCH   | DALLAS   |     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)

mysql> select * from dept a join dept b;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME      | LOC      | DEPTNO | DNAME      | LOC      |
+--------+------------+----------+--------+------------+----------+
|     40 | OPERATIONS | BOSTON   |     10 | ACCOUNTING | NEW YORK |
|     30 | SALES      | CHICAGO  |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |     10 | ACCOUNTING | NEW YORK |
|     10 | ACCOUNTING | NEW YORK |     10 | ACCOUNTING | NEW YORK |
|     40 | OPERATIONS | BOSTON   |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |     20 | RESEARCH   | DALLAS   |
|     20 | RESEARCH   | DALLAS   |     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |     30 | SALES      | CHICAGO  |
|     30 | SALES      | CHICAGO  |     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |     30 | SALES      | CHICAGO  |
|     10 | ACCOUNTING | NEW YORK |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |     40 | OPERATIONS | BOSTON   |
|     20 | RESEARCH   | DALLAS   |     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+--------+------------+----------+
16 rows in set (0.00 sec)

mysql> select a.* from dept a,dept b;    mysql> select b.* from dept a,dept b;
+--------+------------+----------+        +--------+------------+----------+
| DEPTNO | DNAME      | LOC      |        | DEPTNO | DNAME      | LOC      |
+--------+------------+----------+        +--------+------------+----------+
|     40 | OPERATIONS | BOSTON   |        |     10 | ACCOUNTING | NEW YORK |
|     30 | SALES      | CHICAGO  |        |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |        |     10 | ACCOUNTING | NEW YORK |
|     10 | ACCOUNTING | NEW YORK |        |     10 | ACCOUNTING | NEW YORK |
|     40 | OPERATIONS | BOSTON   |        |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |        |     20 | RESEARCH   | DALLAS   |
|     20 | RESEARCH   | DALLAS   |        |     20 | RESEARCH   | DALLAS   |
|     10 | ACCOUNTING | NEW YORK |        |     20 | RESEARCH   | DALLAS   |
|     40 | OPERATIONS | BOSTON   |        |     30 | SALES      | CHICAGO  |
|     30 | SALES      | CHICAGO  |        |     30 | SALES      | CHICAGO  |
|     20 | RESEARCH   | DALLAS   |        |     30 | SALES      | CHICAGO  |
|     10 | ACCOUNTING | NEW YORK |        |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |        |     40 | OPERATIONS | BOSTON   |
|     30 | SALES      | CHICAGO  |        |     40 | OPERATIONS | BOSTON   |
|     20 | RESEARCH   | DALLAS   |        |     40 | OPERATIONS | BOSTON   |
|     10 | ACCOUNTING | NEW YORK |        |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+        +--------+------------+----------+
16 rows in set (0.00 sec)                16 rows in set (0.00 sec)

mysql> select * from dept a,dept b where a.deptno=b.deptno;
+--------+------------+----------+--------+------------+----------+
| DEPTNO | DNAME      | LOC      | DEPTNO | DNAME      | LOC      |
+--------+------------+----------+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+--------+------------+----------+
4 rows in set (0.00 sec)

 Approach 1

select * from Employee a,Employee b;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId | Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         | 1  | Joe   | 70000  | 3         |
| 3  | Sam   | 60000  | NULL      | 1  | Joe   | 70000  | 3         |
| 4  | Max   | 90000  | NULL      | 1  | Joe   | 70000  | 3         |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 2  | Henry | 80000  | 4         |
| 2  | Henry | 80000  | 4         | 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      | 2  | Henry | 80000  | 4         |
| 4  | Max   | 90000  | NULL      | 2  | Henry | 80000  | 4         |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 3  | Sam   | 60000  | NULL      |
| 2  | Henry | 80000  | 4         | 3  | Sam   | 60000  | NULL      |
| 3  | Sam   | 60000  | NULL      | 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      | 3  | Sam   | 60000  | NULL      |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 4  | Max   | 90000  | NULL      |
| 2  | Henry | 80000  | 4         | 4  | Max   | 90000  | NULL      |
| 3  | Sam   | 60000  | NULL      | 4  | Max   | 90000  | NULL      |
| 4  | Max   | 90000  | NULL      | 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+----+-------+--------+-----------+

select * from Employee a,Employee b where a.ManagerId=b.Id;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId | Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 3  | Sam   | 60000  | NULL      |
| 2  | Henry | 80000  | 4         | 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+----+-------+--------+-----------+

SELECT * FROM Employee a,Employee b WHERE a.ManagerId=b.Id AND a.Salary>b.Salary;
+----+-------+--------+-----------+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId | Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         | 3  | Sam   | 60000  | NULL      |
+----+-------+--------+-----------+----+-------+--------+-----------+

 Approach 2 

Employee                                Leader
+----+-------+--------+-----------+    +----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |    | Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+    +----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |    | 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |    | 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |    | 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |    | 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+    +----+-------+--------+-----------+

select a.name Employee from Employee a join Employee b ON a.ManagerId=b.Id AND a.salary>b.salary;

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
+----+-------+--------+-----------+

SDS_19
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
DeFi智能合约安全审计报告 - earning.farm1
08-04
1. 概要1 2. 审计方法2 3. 项目背景3 4. 代码概述7 5. 审计结果 22 6. 声明 22 1. 概要 2. 审计方法 3. 项目背景
LeetCode: 181. Employees Earning More Than Their Managers
杨领well的专栏
08-20 216
LeetCode: 181. Employees Earning More Than Their Managers 题目描述 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager...
[leetcode]181. Employees Earning More Than Their Managers
weixin_32135877的博客
06-07 136
[leetcode]181. Employees Earning More Than Their Managers Analysis 心情复杂~—— [ummmmmm~] Given the Employee table, write a SQL query that finds out employees who earn more than their managers. ...
LeetCode181 Employees Earning More Than Their Managers
李歇特冯-兹拜因巴哈的博客
03-10 110
待续。。。
181. Employees Earning More Than Their Managers(Lettcode)
卷毛的博客
11-03 156
The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Name | Salary |
Listen this way 2-6 earning and spending money wisely.doc
12-14
Listen this way 2-6 earning and spending money wisely.doc
earning-credit
07-01
自述 此自述文件通常会记录启动和运行应用程序所需的任何步骤。 您可能想要涵盖的内容： Ruby版 系统依赖 配置 数据库创建 数据库初始化 如何运行测试套件 服务（作业队列、缓存服务器、搜索引擎等） ...
daltonlearninglab-machine-earning-extensions
05-11
daltonlearninglab-machine-earning扩展 教孩子学习机器学习时使用的扩展列表 -道尔顿学习实验室采用以学生为中心的模型，在该模型中，孩子们可以通过创造性地解决复杂的现实世界中的问题而获得宝贵的知识和有用的...
发表在Science上的机器学习论文
10-05
Vicarious发表在Science上的论文，有兴趣的可以了解一下
【Leetcode】181. Employees Earning More Than Their Managers （Easy）
不想做菜鸟的小螃蟹
02-01 221
1.题目 The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. +----+-------+--------+-----------+ | Id | Name
[LeetCode]Employees Earning More Than Their Managers,解题报告
小一的专栏
04-13 4817
目录目录 题目 思路 AC SQL题目The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id. Id Name Salary ManagerId 1 Joe
LeetCode 181. Employees Earning More Than Their Managers 超过经理收入的员工（数据库）
qq_41878358的博客
02-13 121
题目： 解答： 解法一： 生成两个实例对象进行内交，通过比对ManagerId和Id，限制条件是一个Salary大于另一个即可 select Name Employee from Employee e1 where e1.Salary> (select e2.Salary from Employee e2 where e1.ManagerId=e2.Id) 解法二： 通过 where 来...
181. Employees Earning More Than Their Managers - 超过经理收入的员工 ＜easy＞
Lazyafei's Blog
10-21 131
Employee表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。 +----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 6...
leetcode 181. Employees Earning More Than Their Managers
有志者事竟成
08-08 1155
181. Employees Earning More Than Their Managers   Question Editorial Solution  My Submissions Total Accepted: 23223Total Submissions: 61926Difficulty: Easy The Employee t
leetcode 181. Employees Earning More Than Their Managers（SQL，相关子查询，自身连接）38
izhxxx的博客
10-20 255
leetcode 181. Employees Earning More Than Their Managers（SQL，相关子查询，自身连接）
#include<iostream> #include<stdlib.h> #include<string> using namespace std; //请在此处完成YearWorker,MonthWorker,WeekWoker及Company类的编码 /***********begin***********/ class Employee { public: virtual double earning()=0; }; class WeekWorker:public Employee { double earning(){ return 580*52; } }; class MonthWorker:public Employee { double earning(){ return 2500*12; } }; class YearWorker :public Employee { double earning(){ return 22000; } }; class Company{ private: Employee* employees[30]; public: void salarypay(); }; void salariesPay() { } } /************end**********/ int main() { Company co; for(int i=0;i<30;i++) { int r=rand()%3+1; switch (r) { case 1: co.emp[i]=WeekWorker(580); break; case 2: co.emp[i]=MonthWorker(2500); break; case 3: co.emp[i]=YearWorker(22000); break; default: break; } } cout<<"company total pay="<<co.salarypay(); return 0; }
最新发布
05-30
total_pay += employees[i]->earning(); } } cout ; } int main() { Company co; for (int i = 0; i ; i++) { int r = rand() % 3 + 1; switch (r) { case 1: co.employees[i] = new WeekWorker(); ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值