【Leetcode Database】Employees Earning More Than Their Managers

最新推荐文章于 2016-07-27 11:30:18 发布

大家叫我陈老师

最新推荐文章于 2016-07-27 11:30:18 发布

阅读量433

点赞数

分类专栏： Leetcode Database 文章标签： mysql database leetcode 数据库 sql

本文链接：https://blog.csdn.net/chenyancun2011/article/details/46454787

版权

Leetcode 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

Database

4 篇文章 0 订阅

订阅专栏

题目：

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   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

代码：

# Write your MySQL query statement below
select a.Name from Employee as a, Employee as b where a.ManagerId=b.Id and a.Salary>b.Salary;

思想：

给 Employee表取两个表别名a,b，条件a.ManagerId=b.Id是找出每个employee对于的manager，a.Salary>b.Salary是找出其中employee工资大于其manager工资的记录。注意，请思考，为什么select后面是a.Name而不是b.Name？这是与我们条件相关的，因为我们的条件中写的是a.ManagerId，也就是说我们认为a用来记录employee，b用来记录manager

本题知识点：

（1）表别名

表别名和自联结

http://www.2cto.com/database/201307/230525.html

本查询所用到的表：

下面的表num代表公共汽车路线，如1路车，2路车，stop带表停车站点，如A站，B站

表：

route;
+------+------+
| num  | stop |
+------+------+
|    1 | A    |
|    1 | B    |
|    1 | C    |
|    2 | B    |
|    2 | C    |
|    2 | D    |
+------+------+

准备知识

1.使用表别名

*表别名只在查询执行中使用

*表别名不返回到客户机

  Example:
  mysql> SELECT * from route AS r1
           -> WHERE r1.num = 1;
+------+------+
| num  | stop |
+------+------+
|    1 | A    |
|    1 | B    |
|    1 | C    |
+------+------+
3 rows in set (0.00 sec)

解析：

相信理解表别名应该不费力，类似与列别名，表别名只是给表取另外一个名字而已，代表的还是相同的表

2.自联结

*自联结通常作为外部语句来代替从相同表中检索数据时使用的子查询语句

*使用表别名能在单条语句中多次使用相同的表

下面给出一个简单的例子帮助理解自联结的原理

Example:

下面的语句查询的结果是共用同一车站的所有公交线

mysql> SELECT DISTINCT r2.num,r2.stop
         -> FROM route AS r1,route AS r2
         -> WHERE r1.stop = r2.stop 
         -> ORDER BY r2.stop;
+------+------+
| num  | stop |
+------+------+
|    1 | A    |
|    1 | B    |
|    2 | B    |
|    1 | C    |
|    2 | C    |
|    2 | D    |
+------+------+
6 rows in set (0.00 sec)

解析：

第一句

mysql> SELECT DISTINCT r2.num,r2.stop
                    -> FROM route AS r1,route AS r2；

为了详解，这里先去除DISTINCT关键字，并只截取下面的子句

 mysql> select * From route AS r1,route r2;
+------+------+------+------+
| num  | stop | num  | stop |
+------+------+------+------+
|    1 | A    |    1 | A    |
|    1 | B    |    1 | A    |
|    1 | C    |    1 | A    |
|    2 | B    |    1 | A    |
|    2 | C    |    1 | A    |
|    2 | D    |    1 | A    |
|    1 | A    |    1 | B    |
|    1 | B    |    1 | B    |
|    1 | C    |    1 | B    |
|    2 | B    |    1 | B    |
|    2 | C    |    1 | B    |
|    2 | D    |    1 | B    |
|    1 | A    |    1 | C    |
|    1 | B    |    1 | C    |
|    1 | C    |    1 | C    |
|    2 | B    |    1 | C    |
|    2 | C    |    1 | C    |
|    2 | D    |    1 | C    |
|    1 | A    |    2 | B    |
|    1 | B    |    2 | B    |
|    1 | C    |    2 | B    |
|    2 | B    |    2 | B    |
|    2 | C    |    2 | B    |
|    2 | D    |    2 | B    |
|    1 | A    |    2 | C    |
|    1 | B    |    2 | C    |
|    1 | C    |    2 | C    |
|    2 | B    |    2 | C    |
|    2 | C    |    2 | C    |
|    2 | D    |    2 | C    |
|    1 | A    |    2 | D    |
|    1 | B    |    2 | D    |
|    1 | C    |    2 | D    |
|    2 | B    |    2 | D    |
|    2 | C    |    2 | D    |
|    2 | D    |    2 | D    |
+------+------+------+------+
36 rows in set (0.00 sec)

从上面的查询结果中可以看出，共有36条记录（刚好是两张表的笛卡尔积，关于笛卡尔积，请点击MySQL学习足迹记录13--联结表），

可一推测，所谓的自联结就是把同一张表，看成独立的，不同的两张表r1,r2

我们的目的是查询共用同一车站的所有公交线，所以从上表的结果集中再添加筛选条件（令车站相等）：

r1.stop = r2.stop

mysql> SELECT * FROM route AS r1,route AS r2
        -> WHERE r1.stop = r2.stop；
    
+------+------+------+------+
| num  | stop | num  | stop |
+------+------+------+------+
|    1 | A    |    1 | A    |
|    1 | B    |    1 | B    |
|    2 | B    |    1 | B    |
|    1 | C    |    1 | C    |
|    2 | C    |    1 | C    |
|    1 | B    |    2 | B    |
|    2 | B    |    2 | B    |
|    1 | C    |    2 | C    |
|    2 | C    |    2 | C    |
|    2 | D    |    2 | D    |
+------+------+------+------+
10 rows in set (0.00 sec)

这已经很接近所需的结果了，但我们只需num,stop两列，再添加

SELECT r2.num,r2.stop （SELECT r1.num,r1.stop也OK）
 mysql> SELECT r2.num,r2.stop 
          -> FROM route AS r1,route r2
         -> WHERE r1.stop = r2.stop;
+------+------+
| num  | stop |
+------+------+
|    1 | A    |
|    1 | B    |
|    1 | B    |
|    1 | C    |
|    1 | C    |
|    2 | B    |
|    2 | B    |
|    2 | C    |
|    2 | C    |
|    2 | D    |
+------+------+
10 rows in set (0.00 sec)

最后去除相同的记录，再按车站排序

mysql> SELECT DISTINCT r2.num,r2.stop
          -> FROM route AS r1,route AS r2
          -> WHERE r1.stop = r2.stop
          -> ORDER BY r2.stop;
+------+------+
| num  | stop |
+------+------+
|    1 | A    |
|    1 | B    |
|    2 | B    |
|    1 | C    |
|    2 | C    |
|    2 | D    |
+------+------+
6 rows in set (0.00 sec)

从结果集中可以看出1路车和2路车共用B，C车站

（2）联结表

http://www.2cto.com/database/201307/230522.html

MySQL学习足迹记录13--联结表--INNER JOIN...ON

学数据库联结表这一节内容几乎吐血，看书上的介绍和百度到的内容，总感到一脸茫然.....也许是我理解能力太差了吧。但我是一个不会轻易放弃的人，研究了半小时，终于搞明白了联结的原理，自我总结如下：关键在于“select语句，如果没有WHERE子句限定，将返回两个表的笛卡尔积.”

Example:
    
表一： Kemu
+-----+--------+
| kno | kname  |
+-----+--------+
| b   | yuwen  |
| b1  | shuxue |
+-----+--------+



表二： Chenji；
+------+------+---------+
| sno  | kno  | chengji |
+------+------+---------+
|    1 | a    |      10 |
|    2 | a1   |      20 |
+------+------+---------+
2 rows in set (0.00 sec)




*重点：select语句，如果没有WHERE子句限定，将返回两个表的笛卡尔积.
mysql> SELECT * FROM Kemu,Chenji;
+-----+--------+------+------+---------+
| kno | kname  | sno  | kno  | chengji |
+-----+--------+------+------+---------+
| b   | yuwen  |    1 | a    |      10 |
| b1  | shuxue |    1 | a    |      10 |
| b   | yuwen  |    2 | a1   |      20 |
| b1  | shuxue |    2 | a1   |      20 |
+-----+--------+------+------+---------+
4 rows in set (0.00 sec)

Tips:SELECT 语句后面的所有表将会合成一个大表，所以，SELECT后所跟的各种子句就相当于在一个表中筛选数据了。

1.外键：某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系

2. 联结不是物理实体，它在实际的数据库表中不存在，它存在与查询执行当中

3.创建联结

  Example:
  mysql> SELECT vend_name,prod_name,prod_price
            -> FROM vendors,products
           -> WHERE vendors.vend_id = products.vend_id        #WHERE子句指示MySQL匹配vendors表的  
           -> ORDER BY vend_name,prod_name;                     #vend_id和products表中的vend_id.

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.01 sec)

TIPS:

*WHERE联结两个表时，实际是将第一个表中的每一行和第二个表中的每一行配对.并过滤不符合条件的行.

4.内部联结INNER JOIN .... ON

  Example:
  mysql> SELECT vend_name,prod_name,prod_price
           -> FROM vendors INNER JOIN products                     #使用明确的联结语法能狗确保不会忘记联结条件
           -> ON vendors.vend_id = products.vend_id                #联结条件用特定的ON子句，
           -> ORDER BY vend_name,prod_name;                        #传递的实际条件与传递给WHERE的相同
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)

5.联结多个表

*SQL对一条SELECT语句中可以联结的表的数目没有限制。

   Example:
    mysql> SELECT prod_name,vend_name,prod_price,quantity
            -> FROM orderitems,products,vendors
            -> WHERE products.vend_id=vendors.vend_id
           -> AND orderitems.prod_id=products.prod_id
           -> AND order_num=20005;
+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+
4 rows in set (0.00 sec)