mysql - 从 SQL 中的多对多关系中查找最大值

Question

我有 3 个表 -
Employee - 有列 - id, name, salary
项目 - 包含列 - id、name、budget
分配 - 具有列 - empId、pid(这些列分别指员工和项目)

Employee table looks like
1| A | 1000
2| B | 2000
3| C | 500
4| D | 4000
5| E | 1000

Project
1| XYZ | 50000
2| PQR | 60000
3| ABC | 70000

Assignment 
1|1
2|1
3|3
4|2
5|2

我希望 SQL 查询返回每个项目中具有最高薪水的员工

结果集应该是这样的-

Project id|Employee Name|Salary
     1    |      B      | 2000
     2    |      D      | 4000
     3    |      C      | 500

我写了下面的 SQL。但它把所有的行都还给我了。有什么问题吗？

select p.id, p.name, e.id, e.name, e.salary
FROM 
(
    select e.id, MAX(e.salary) 
    from employee e
    join assignment a on e.id = a.empId
    join project p on p.id = a.pid
    group by e.id
 ) as EmpMAX
 join Employee e on EmpMAX.id = e.id
 join assignment a on a.empId = e.id
 join project p on p.id = a.pid

我引用了以下链接来提出 SQL SQL Query between 3-tables of a many-to-many relationship

我正在使用 MySQL

Answer 1

可能最简单的方法是 substring_index()`group_concat()` 技巧:

select p.projid,
       substring_index(group_concat(e.name order by e.salary desc), ',', 1) as empname,
       max(salary)
from employee e join
     assignment a
     on e.id = a.empId
group by p.projid;

注意:如果有多个员工的薪水相同，这只会选择一个员工。