SQL查询:选择每个公司最高薪资的员工，

Question

您好，我已经为Derby数据库编写了一个SQL查询，该查询从每个具有最高工资的公司获取一个员工

这些是表结构

 create table Company_Tbl_One(
    company_Id int primary key,
    company_name varchar(100)
 )

 create table Employee_Tbl_One(
    employee_Id int primary key,
    employee_name varchar(100),
    company int references Company_Tbl_One
 )

 alter table Employee_Tbl_One add salary int

 insert into Company_Tbl_One values(12,'Facebook Inc');
 insert into Company_Tbl_One values(11,'Google Inc');
 insert into Company_Tbl_One values(10,'Yahoo Inc');
 insert into Company_Tbl_One values(14,'AOL Inc');

 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(1, 'Tom Jackson',12,1000);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(2, 'Jimmy John',12,200);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(3, 'Samual Jackson',11,2000);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(4, 'Sam Raime',10,3000);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(5, 'Tidy Mann',14,5000);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(6, 'Oliver Stone',14,5300);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(7, 'Falcon Gonzalies',10,3300);
 insert into EMPLOYEE_TBL_ONE(employee_Id,employee_name,company,salary) values(8, 'Ister Jack',11,2300);

sql查询如下所示

select e.employee_name, c.company_name
from    EMPLOYEE_TBL_ONE e,
    COMPANY_TBL_ONE c,
    (select max(salary) as SALARY,
            company as COMPANY_ID 
            from EMPLOYEE_TBL_ONE group by COMPANY) x
where   c.company_Id = x.COMPANY_ID 
and     e.salary = x.SALARY
and     e.company = c.company_Id

现在，上面的sql查询可以很好地工作并获取结果，

但是，对于相同的问题语句，有没有其他编写sql查询的方法呢？

Answer 1

另一种使用not exists的方法，即在不存在其他高薪员工的情况下选择所有员工

select e.employee_name, c.company_name, e.salary
from EMPLOYEE_TBL_ONE e
join COMPANY_TBL_ONE c on e.company = c.company_Id     
where not exists (
    select 1 from EMPLOYEE_TBL_ONE e2
    where e2.company = e.company
    and e2.salary > e.salary
)

Answer 2

您可以使用SQL的函数RANK

SELECT *
FROM Company_Tbl_One C
INNER JOIN 
(
  SELECT *, RANK() OVER(PARTITION BY Company ORDER BY Salary DESC) AS rank
  FROM Employee_Tbl_One
) E
ON C.company_Id = E.company
AND E.rank = 1

编辑:对不起，我以为是MSSQL