MySQL -将表连接到其自身/子查询

Question

我在一个我最近犯了错的评估问题上寻求帮助，我尝试了许多解决方案，认为我知道我想做什么，但似乎无法理解语法。

我有一张看起来像下面的桌子，但有更多的记录。

MyTable
ID   Name            DivisionID   ManagerID   Salary
123  John Smith      100          789         40000
456  Harold Johnson  101          null        60000
789  Vicky Brown     100          null        80000

我不得不选择第三高工资的人，我对此没有异议。但是，我还需要返回Manager名称，而不是ManagerID，它需要从同一个表中查找。

我尝试了以下解决方案，该解决方案似乎有点不雅致，并且必须在其中使用相同的硬编码查询，因此不适合缩放或一般使用：

SELECT 
table.ID, 
Name,
DivisionID,
(SELECT 
    Name FROM table WHERE id=(
        SELECT ManagerID FROM table ORDER BY Salary DESC LIMIT 2,1)
) AS ManagerName,
Salary

FROM table
ORDER BY Salary DESC LIMIT 2,1;

我认为，对于子查询，可能有一些方法可以做到这一点，例如，首先在对仅为manager id和name的查询中选择一个单独的表，然后从其中进行选择--但我似乎无法正确地理解语法，也无法理解它。我认为也可以使用表别名，在不同的别名下从相同的表中选择两个不同的结果，然后将两者连接起来，但同样无法解决如何做到这一点。下面是我试图用别名做的事情

SELECT 
a.ID, 
a.Name,
a.DivisionID,
b.Name AS ManagerName
a.Salary

FROM table a
INNER JOIN table b ON a.ManagerID=b.ID
ORDER BY Salary DESC LIMIT 2,1;

Answer 1

首先，当被要求归还第九大/最不值钱的时候，你必须问回在关系发生时该做什么。他们想要的是工资第三高的人，所以薪水1000,1000,900,900,800,800,700,600,500，我想你想要还那些挣到800的人，因为那是第三高的工资。如果你只是按工资排序，跳过两个，拿第三个，然后你任意挑选一个薪水900的人，900甚至不是第三最高的，而是第二最高的。

为了得到经理，只需再次加入表。对于薪资第三高的员工本身就是经理的情况，您应该使用外部联接。

直接向前的解决方案是使用DENSE_RANK对行进行排序。

select *
from
(
  select t.*, dense_rank() over (order by salary desc) as rnk
  from mytable t
) employee
left join mytable manager on manager.id = employee.managerid
where employee.rnk = 3;

MySQL从8版开始就支持DENSE_RANK。在旧版本中，您必须再次查找相同的表。选择不同的薪资，并在这些saleries上使用限制/偏移子句。

select *
from mytable employee
left join mytable manager on manager.id = employee.managerid
where employee.salary =
(
  select distinct salary
  from mytable
  order by salary desc
  limit 2, 1
);

演示：8.0&fiddle=6b17e369fcd4f99ddc6c268de15f08a1