通过另一个表的MAX()值对MySQL结果集进行排序 [英] Ordering a MySQL result set by a MAX() value of another table

问题描述

假设我有两个桌子.

表1:员工名册

| ID | Name        |
| ---| ------------|
|  1 | Joe         |
|  2 | Jim         |
|  3 | Jane        |
|  4 | Jill        |
|  5 | Dilbert     |

表2:每月薪水

| Check # | Employee ID | Amount paid |
|---------|-------------|-------------|
| 11235   |      3      |  2000       |
| 51239   |      2      |  3000       |
| 72244   |      5      |  6500       |
| 61633   |      2      |  2300       |
| 14234   |      1      |  2900       |
| 91335   |      1      |  4500       |
| 13736   |      4      |  3000       |
| 41234   |      5      |  5000       |
| 71232   |      4      |  10000      |
| 88234   |      2      |  6000       |
| 23039   |      3      |  1200       |
| 42534   |      2      |  9000       |
| 74834   |      3      |  1230       |
| 38034   |      5      |  9900       |
| 91334   |      2      |  1280       |
| 24134   |      1      |  2000       |

因此，基本上，我们有5名员工和一张表，该表跟踪我们每月支付给他们多少薪水.

So basically we have 5 employees and a table which keeps track of how much we pay them every month.

我需要进行查询，以返回所有其BEST PAYCHECK EVER订购的员工.

I need to make a query that would return all employees ordered by their BEST PAYCHECK EVER.

像……吉尔曾经获得10000的薪水，这使她在结果集中排名第一.迪尔伯特(Dilbert)应该排名第二，因为他曾经获得过9900的薪水.等等.

Like... Jill once had a 10000 paycheck, that makes her #1 on the result set. Dilbert should be #2 as he once got a 9900 paycheck. Etc.

对于具有数以千万计的条目的表，它应该足够快

提前谢谢！

推荐答案

select
  p.ID,
  e.NAME
from
  Paychecks p
  inner join Employee e on p.EmployeeID = e.ID
group by
  p.ID
order by
  max(p.AmountPaid) desc

另一种书写方式看起来更合逻辑，但可能更慢(必须测试):

A different way of writing, which looks more logical, but may be slower (you'd have to test) is:

select
  e.ID,
  e.NAME
from
  Employee e
  inner join Paychecks p on p.EmployeeID = e.ID
group by
  e.ID
order by
  max(p.AmountPaid) desc

在具有数千万行的情况下，每个查询有时会变慢，但是有了适当的索引，它的速度就可以了.我认为您基本上需要在Paychecks.EmployeeID和Paychecks.AmountPaid上结合一个索引.并且对Employee.ID进行索引可能会有所帮助.

With tens of millions of rows, every query is growing slow sometimes, but with the proper indexes, this is as fast as it gets. I think you basically need one index on Paychecks.EmployeeID and Paychecks.AmountPaid combined. And index on Employee.ID may help.

如果联接最终使您丧命，则可以执行两个查询.第一个仅使用薪水检查按EmployeeID分组，并按max(PaycheckAmount)对其进行排序，第二个可用于获取每个ID的名称.有时候，加入会带来比您想要的性能更高的绩效，当您为500名员工获得1000万薪水时，分两步进行可能会更快，尽管这意味着他们已经在公司工作了1600年左右. ;-)

If the join is killing you in the end, you may execute two queries. The first one only uses the paychecks to group them by EmployeeID and order them by the max(PaycheckAmount), and a second one can be used to fetch the names for each ID. Sometimes joins cost more performance than you'd like, and when you got 10 million paychecks for 500 employees, it may be faster to do it in two steps, although it will mean that they have been working at the company for about 1600 years avarage. ;-)

这篇关于通过另一个表的MAX()值对MySQL结果集进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！