mysql - 根据另一个列 ID 选择列的最大值

Question

我试图在具有 teamID、salary、yearID 属性的表中获取每年的最高工资。应该不难吧？这是我的查询:

SELECT teamID, MAX(tS.teamSalary), yearID
FROM
  (SELECT
      teamID,
      sum(salary) AS teamSalary,
      yearID
    FROM salaries
    GROUP BY teamID, yearID) tS
GROUP BY yearID;

内部查询工作得很好，但外部查询只是报告每个组的第一个 teamID。我做错了什么？

Inner query output:
A  1  2000
B  1  2000 
C  2  2000
A  2  2001
B  3  2001
A  2  2002
B  2  2002

Full query output: 
A  1  2000
A  2  2001
A  2  2002

Desired output: 
C  2  2000
B  3  2001
A  2  2002

Answer 1

首先你会得到每年的 maxSalary，然后你会得到额外的信息:

SELECT teamID, salary, yearID
  FROM salaries 
  JOIN
    (SELECT MAX(salary) AS maxSalary,
            yearID
       FROM salaries
   GROUP BY yearID) tS
    ON ts.yearID = salaries.yearID
   AND ts.maxSalary = salaries.salary

编辑:不确定您是否想要某年的最高工资，然后它属于哪个团队，或者您是否想要按团队和年份划分的最高工资。第二个选项在这里:

  SELECT MAX(salary) AS maxSalary, yearID, teamID
    FROM salaries
GROUP BY yearID, teamID