如何选择部门前3名的薪水？

Question

我正在尝试在线解决具有以下 2 个表的 SQL 问题，

员工 表

Employee 表包含所有员工。

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

部门 表

SQL 应返回以下数据

我有如下 SQL 查询，

     SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS Salary
FROM Employee E INNER JOIN Department D ON E.DepartmentId = D.Id
WHERE (SELECT COUNT(DISTINCT(Salary)) FROM Employee
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3
ORDER by E.DepartmentId, E.Salary DESC;

SQL 很好，但是，我对 < 3 部分有点困惑。不应该是= 3，就像被问及前3名薪水的问题一样吗？我很欣赏对 SQL 的解释，并且会有所帮助。

原文由 Arefe 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如果您添加了一列 Count employees who earn more 您的表格将如下所示

+----+-------+--------+--------------+-------------------------------+
| Id | Name  | Sa1ary | DepartmentId | Count employees who earn more |
+----+-------+--------+--------------+-------------------------------+
|  1 | Joe   |  70000 |            1 |    2                          |
|  2 | Henry |  80000 |            2 |    0                          |
|  3 | Sam   |  60000 |            2 |    1                          |
|  4 | Max   |  90000 |            1 |    0                          |
|  5 | Janet |  69000 |            1 |    3                          |
|  6 | Randy |  85000 |            1 |    1                          |
+----+-------+--------+--------------+-------------------------------+

然后找到每个部门的前 3 名。你的 WHERE 会是

WHERE `Count employees who earn more` < 3

如果您有 =3 它将返回唯一排名第四的员工

由于您没有该列，这就是该 SQL 所做的

(SELECT COUNT(DISTINCT(Salary)) FROM Employee
       WHERE DepartmentId = E.DepartmentId AND Salary > E.Salary)

如果您想生成上述表格，您可以执行以下操作

SELECT
      D.Name AS Department,
      E.Name AS Employee,
      E.Salary AS Salary,
      Count(E2.Salary) as Count_employees_who_earn_more
FROM Employee E
INNER JOIN Department D
ON E.DepartmentId = D.Id
LEFT JOIN Employee E2 ON
    e2.DepartmentId = E.DepartmentId
    AND E2.Salary > E.Salary
GROUP BY  D.Name ,
      E.Name ,
      E.Salary

演示

原文由 Conrad Frix 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

WITH tbl
     AS (SELECT NAME,
                salary,
                departmentid,
                Dense_rank()
                  OVER (
                    partition BY departmentid
                    ORDER BY salary DESC) AS nums
         FROM   employee)
SELECT d.NAME   Department,
       tbl.NAME Employee,
       tbl.salary
FROM   tbl
       INNER JOIN department d
               ON tbl.departmentid = d.id
WHERE  tbl.nums <= 3

原文由 Anish 发布，翻译遵循 CC BY-SA 4.0 许可协议