SQL 非经理员工名单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24725228/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List of employees who are not managers
提问by user3834674
I would like to pull list of employees who are not managers...
我想拉出不是经理的员工名单...
Have a look at below two queries post if you have a better query...
如果您有更好的查询，请查看下面的两个查询帖子...
Query 1:
查询 1：
SELECT Empno, Ename, Job, Sal
FROM Emp
WHERE Empno NOT IN (SELECT NVL(MGR,0) FROM Emp)
Query 2:
查询 2：
SELECT Empno, Ename, Job, Sal
FROM Emp
WHERE Empno NOT IN (SELECT DISTINCT MGR FROM Emp WHERE MGR IS NOT NULL)
回答by Brian DeMilia
This may run faster depending on what fields are indexed:
这可能会运行得更快，具体取决于索引的字段：
select x.empno, x.ename, x.job, x.sal
from emp x
left join emp y
on x.empno = y.mgr
where y.mgr is null
Uses an outer join with the same table and filters in on rows where the empno never appears as a value in the mgr column of the table.
对同一个表使用外部联接，并筛选出 empno 从未在表的 mgr 列中显示为值的行。
回答by user3834750
Using a oracle specific function:
使用 oracle 特定功能：
select Empno, Ename, Job, Sal from Emp
where CONNECT_BY_ISLEAF = 1
start with MGR is null
connect by prior Empno = MGR;
For see an example in how to use it see http://sqlfiddle.com/#!4/858e0/3
有关如何使用它的示例，请参见http://sqlfiddle.com/#!4/858e0/3
But if there's no hierarchy of managers is easier:
但是，如果没有经理层级，则更容易：
SELECT Empno, Ename, Job, Sal FROM Emp WHERE MGR IS NOT NULL
回答by linda.y.ll
select * from emp a where not exists (select 1 from emp where manager_id =a.emp_id);
select * from emp a where not exist (select 1 from emp where manager_id =a.emp_id);
above sql will find all the employee who is not manager, and exists function having better performance that not in
上面的sql会找到所有不是经理的员工，并且存在性能更好的函数而不是
回答by Aman Goel
check it below:
请在下面检查：
SELECT E.LAST_NAME,E.EMPLOYEE_ID FROM EMPLOYEES EWHERE EMPLOYEE_ID not in
(SELECT MANAGER_ID FROM EMPLOYEES where MANAGER_ID is not null)
回答by user9124789
SELECT * FROM employees
WHERE id <> managerid
回答by Thang Pham
This is the best solution that cover all cases
这是涵盖所有情况的最佳解决方案
select e.name
from employees as e
left join employees as m
on e.id = m.managerId
where m.managerId is null
回答by Prathm
Try this query it will work
试试这个查询它会起作用
SELECT Empno, Ename, Job, Sal FROM Emp WHERE Job != 'MANAGER'
SELECT Empno, Ename, Job, Sal FROM Emp WHERE Job != 'MANAGER'
of
的
SELECT Empno, Ename, Job, Sal FROM Emp WHERE Job NOT LIKE 'MANAGER'
SELECT Empno, Ename, Job, Sal FROM Emp WHERE Job NOT LIKE 'MANAGER'
回答by Jakiya Mulla
create table empinfo(last_name varchar2(20),job_title varchar2(20));
insert into empinfo (last_name,job_title)values('Peterson','Manager');
insert into empinfo (last_name,job_title)values('Pollard','Project Engineer');
insert into empinfo (last_name,job_title)values('Taylor','Developer');
insert into empinfo (last_name,job_title)values('Mlinga','Tester');
select last_name,job_title from empinfo where job_title NOT LIKE 'Manager';
