Oracle SQL子查询

debugcn 发表于 Dev

用户名

显示员工last_name，salary以及job在部门IT或MARKETING部门工作并且比部门中收入最差的人挣得多的人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'));

乌察夫

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

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。