铁轨。使用max（）选择记录 [英] rails. select records with max()

问题描述

我有一个这样的表格，我想返回薪水最高的前两个人（按名称），并返回薪水相应的记录。这是表格

I have a table like this, and I want to return the top two person (by name) with the highest salary, and also the record with the corresponding salary. Here is the table

id, name, salary
1, Tom, 200
2, Tom, 300
3, Bob, 400
4, Bob, 500
5, Alice, 600
6, Alice, 700

我使用了此命令

Employer.select("employers.*, max(employers.salary) as maxsalary").group("employers.name").order("maxsalary desc").limit(2)

期望的回报：

id, name, salary
6, Alice, 700
4, Bob, 500

我得到的似乎是

id, name, salary
5, Alice, 600
3, Bob, 400

是否有选择响应最大值的记录？任何评论/答案深表感谢。

Is there anyway to select the records responding to the max ? Any comment/answer is much appreciated.

推荐答案

这个问题实际上非常棘手！
看起来很简单，但事实并非如此。

This Question is very tricky actually! It seems very easy but it's not.

查询：

Employer.joins(%Q|
  LEFT JOIN employers as e 
  ON 
  e.name = employers.name 
  AND 
  employers.salary < e.salary
|).where('e.salary IS NULL').order('employers.salary DESC').limit(2)

该怎么做！（我去过那里）

我们想要以确保我们只为每个雇主获得最高薪水，然后获得其中的最高2薪。

We want to make sure that we only have the highest salary for each employer and then get the highest 2 of those.

一些理论资料（如果只想了解查询内容，请跳过此部分）

Some Theoretical Stuff (skip this part if you only want to understand the query)

让Salary作为函数S（name，id）返回给出名称和ID的值
为了证明给定的薪水（S（name，id））是最高的，我们必须证明
我们想证明

Let Salary be a function S(name,id) where it returns a value given the name and id To prove that the given salary (S(name,id)) is the highest we have to prove that We want to prove either

• ∀xS（name，id）> S（name，x）（此薪水高于该薪水的所有其他
  薪水我）

OR

• ¬∃ x S（名称，ID）< S（name，x）（
  这个名字没有更高的薪水）

第一种方法需要我们得到我不喜欢的那个名字的所有记录。

The first approach will need us to get all the records for that name which I do not really like.

第二个需要一种聪明的方式来说没有比这个更高的记录了。

The second one will need a smart way to say there can be no record higher than this one.

返回SQL

如果我们离开表，姓名和薪水均小于联接表：

If we left joins the table on the name and salary being less than the joined table:

%Q|
      LEFT JOIN employers as e 
      ON 
      e.name = employers.name 
      AND 
      employers.salary < e.salary
    |

我们确保将具有相同薪水的另一条记录的所有记录都加入同一用户：

we make sure that all records that has another record with higher salary for the same user to be joined:

employers.id, employers.name, employers.salary, e.id, e.name, e.salary
1           , Tom           , 200             , 2   , Tom   , 300
2           , Tom           , 300
3           , Bob           , 400             , 4   , Bob   , 500
4           , Bob           , 500
5           , Alice         , 600             , 6   , Alice   , 700
6           , Alice         , 700

帮助我们筛选不需要分组的每个雇主的最高薪水：

That will help us filter for the highest salary for each employer with no grouping needed:

where('e.salary IS NULL')

employers.id, employers.name, employers.salary, e.id, e.name, e.salary
2           , Tom           , 300
4           , Bob           , 500
6           , Alice         , 700

现在我们需要o是排序：

Now all we need to do is sort:

order('employers.salary DESC')

employers.id, employers.name, employers.salary, e.id, e.name, e.salary
6           , Alice         , 700
4           , Bob           , 500
2           , Tom           , 300

然后限制

limit(2)

employers.id, employers.name, employers.salary, e.id, e.name, e.salary
6           , Alice         , 700
4           , Bob           , 500

这就是我们需要的答案。

And that's the answer we need.

我们为什么不

1。

Employer.order('salary desc').limit(2)

因为这将使我们获得薪水最高的记录，而与名称无关

Because this will get us the records with the highest salaries independent of the name

employers.id, employers.name, employers.salary
5           , Alice         , 600
6           , Alice         , 700

2。

Employer.select('DISTINCT(name)').order('salary desc').limit(2)

问题在于它只会保留名称的第一个出现，然后进行排序

The problem with that is it will only keep the first appearance of the name then sort

employers.id, employers.name, employers.salary
1           , Tom           , 200
3           , Bob           , 400
5           , Alice         , 600

这篇关于铁轨。使用max（）选择记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！