我有两张桌子:

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/

10-12 00:32
查看更多