我有两张桌子:
EMPLOYEES
=====================================================
ID NAME SUPERVISOR LOCATION SALARY
-----------------------------------------------------
34 John AL 100000
17 Mike 34 NY 75000
5 Alan 34 LE 25000
10 Dave 5 NY 20000
BONUS
========================================
ID Bonus
----------------------------------------
17 5000
34 5000
10 2000
我必须编写一个查询,返回每个地点工资最高的雇员的名单,包括他们的姓名、工资和工资+奖金。排名应该基于工资加奖金。所以我写了这个问题:
select em.name as name, em.salary as salary, bo.bonus as bonus, max(em.salary+bo.bonus) as total
from employees as em
join bonus as bo on em.empid = bo.empid
group by em.location
但是我得到了错误的名字,查询不会返回一个没有奖金的员工(empid=5,在employees表中),这个员工的薪水是按地点计算的最高的(25000+0奖金)。
最佳答案
你也可以
select
em.location,
em.name as name,
em.salary as salary,
IFNULL(bo.bonus,0)) as bonus,
max(em.salary+IFNULL(bo.bonus,0)) as total
from employees as em
left join bonus as bo on em.empid = bo.empid
group by em.location;
但是,此查询依赖于特定于MySQL的按行为分组,并且在大多数其他数据库中都会失败(如果启用了
ONLY_FULL_GROUP_BY
设置,则在MySQL的更高版本中也会失败)。我建议改为下面这样的问题:
select
em.location,
em.name as name,
em.salary as salary,
IFNULL(bo.bonus,0)) as bonus,
highest.total
from employees as em
left join bonus as bo on em.empid = bo.empid
join (
select
em.location,
max(em.salary+IFNULL(bo.bonus,0)) as total
from employees as em
left join bonus as bo on em.empid = bo.empid
group by em.location
) highest on em.LOCATION = highest.LOCATION and em.salary+IFNULL(bo.bonus,0) = highest.total;
在这里,您可以确定每个位置的最高工资+奖金,并将结果用作联接中的派生表,以筛选出每个位置的合计最高的员工。
看这个SQL Fiddle
关于mysql - 从一栏到第二栏选择最大值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28497850/