Oracle SQL subquery

Question

显示在IT或MARKETING部门工作的员工last_name, salary和job，其收入高于ACCOUNTING部门中收入最低的员工。按姓氏字母顺序对输出进行排序。

我的答案:

select 
    last_name, salary, job_id 
from 
    employees 
where 
    salary > (select min(salary) 
              from employees 
              where department_id = (select department id 
                                     from departments 
                                     where department_name like'IT' 
                                        or like 'Marketing'));

Answer 1

不确定您想要什么，但听起来是这样的。根据需要在select查询中添加其他列

http://sqlfiddle.com/!9/cfb31c/5/0

select e.last_name,e.salary,e.department_id from
employee e inner join department d
on e.department_id=d.department_id
where d.department_name in ('IT','MARKETING')
and e.salary > (select min(e1.salary) from employee e1
                inner join department d1
                where e1.department_id=d1.department_id
                and d1.department_name = 'ACCOUNTING')
order by e.last_name

相同的语句，重新格式化。也删除了inner关键字和固定的on子句，所以SQL是供应商中立的，即工作在MySQL, MS SQL, Oracle, PostgreSQL，…

select e.last_name, e.salary, e.department_id
  from employee e
  join department d on d.department_id = e.department_id
 where d.department_name in ('IT','MARKETING')
   and e.salary > ( select min(e1.salary)
                      from employee e1
                      join department d1 on d1.department_id = e1.department_id
                     where d1.department_name = 'ACCOUNTING' )
 order by e.last_name