15°

找出部门工资前三高的员工

找出部门工资前三高的员工

员工表:

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+

部门表:

+----+----------+
| Id | Name     |
+----+----------+
| 1  | IT       |
| 2  | Sales    |
+----+----------+

公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大。

select e1.Name as 'Employee', e1.Salary from Employee e1
where 3 >
(
    select count(distinct e2.Salary)
    from Employee e2
    where e2.Salary > e1.Salary
)

然后,我们需要把表 Employee 和表 Department 连接来获得部门信息。

SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )

执行结果如下:

| Department | Employee | Salary |
|------------|----------|--------|
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |

技能点

子查询

mysql子查询常用有两类:嵌套子查询、相关子查询

嵌套子查询:执行不依赖与外部的查询。
(1)执行子查询,其结果不被显示,而是传递给外部查询,作为外部查询的条件使用。
(2)执行外部查询,并显示整个结果。

相关子查询:依赖于外部查询。多数情况下是子查询的WHERE子句中引用了外部查询的表。
(1)从外层查询中取出一个元组,将元组相关列的值传给内层查询。
(2)执行内层查询,得到子查询操作的值。
(3)外查询根据子查询返回的结果或结果集得到满足条件的行。
(4)然后外层查询取出下一个元组重复做步骤1-3,直到外层的元组全部处理完毕。

笛卡尔积

两张表join,行数为 m x n 。当前例子employee表join会产生49行数据。在上面的相关子查询中,外层查询中取出一个元组(一行数据)来与子查询里面的表进行笛卡尔积。

本文由【HelloRookie】发布于开源中国,原文链接:https://my.oschina.net/u/2381372/blog/3137012

全部评论: 0

    我有话说: