检索info sql查询

Question

SELECT  *
FROM    EMPLOYEE a
        LEFT JOIN EMPLOYEE b
            ON a.Employee_ID <> b.Employee_ID
WHERE   a.employee_salary < b.employee_salary and a.Department_ID='30'

您好，我想检索比30部门的员工挣更多钱的员工的所有信息

image of database

Answer 1

SELECT  * FROM    EMPLOYEE 
where (Department_ID<>'30')
and 
 (
 employee_salary > 
  (select max(employee_salary) from EMPLOYEE  where  Department_ID='30')
 )

或者，如果您需要的员工收入超过所有部门30，请使用SUM而不是MAX。

Answer 2

拆分问题将对我们有所帮助：

employees.

• Second：你需要知道离职金是多少钱，而不是这些钱能赚多少钱。

最简单的方法是使用子查询进行查询。主查询检索超过X的雇员，子查询返回X。

子查询离任30名员工赚多少钱？

Select employee_salary from employee where Department_ID='30'

带有子查询的主查询：收入超过此金额的员工：

Select * from employee 
where employee_salary > ANY (
   Select employee_salary from employee where Department_ID='30'
)

请注意，您可以在子查询运算符中使用ANY或ALL：

• ALL表示您正在寻找的员工的收入超过dpt 30的ALL。employees.
• ANY表示您正在寻找的员工的收入超过dpt 30 employees的ANY。

Answer 3

SELECT * fROM EMPLOYEE a where a.Department_ID!='30' 
and (a.employee_salary > (select max(b.employee_salary) 
from EMPLOYEE b where b.Department_ID='30'))