sql - 查询获取员工的薪水

Question

 Insert into Employee values (1,'Abdul Rehman','Street No 12','Kamra Kalan')
 Insert into Employee values (2,'Iram Bhatti','Street No 10','Attock')
 Insert into Employee values (3,'Danial Aziz','Street No 12','Kamra Kalan')
 Insert into Employee values (4,'Kashif Butt','Street No 10','Attock')
 Insert into Employee values (5,'Zohaib Butt','Street No 13','Peshawar')


 insert into Company values (1,'First Bank Co-Operation','Hydrabaad');
 insert into Company values (2,'Small Bank Co-Operation','Kashmir');



 Insert  into Works  values (1,2,5000)
 Insert  into Works  values (2,1,40000)
 Insert  into Works  values (1,3,56000)
 Insert  into Works  values (1,4,8000)
 Insert  into Works  values (2,2,78000)

问题:编写一个查询，以获取比 Small Bank Co operation 的每个员工收入都多的员工的姓名。

我的查询解决方案:

 Select Employee.person_name from Works
 inner join Employee on Employee.person_Id
 =Works.Person_Id inner join Company on
 Company.Company_Id=Works.Company_Id
and  Salary>(Select Salary from works 
 Where Company.Company_name='Small Bank Co-Operation')

但是这个查询对我不起作用我怎么才能得到这个呢？

Answer 1

我认为最简单的解决方案就是连接所有表，然后从 works 表中找到工资等于最高工资的员工。

SELECT e.person_name
FROM employee e inner join works w
on e.person_Id = w.Person_Id
inner join company c
on w.Company_Id = c.Company_Id
WHERE c.Company_name = 'Small Bank Co-Operation'
and w.Salary = (SELECT max(works.Salary)
                FROM works)