SQL SQL查询-Dept和Employee表，有些部门可能没有经理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25701920/
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
SQL query - Dept and Employee table, some departments may not have managers
提问by Avinash
There are two tables:
有两个表：
Employee
---------
emp_id
emp_name
emp_contact
salary
mgr_emp_Id
Dept_No
Dept
-----
Dept_No
Dept_name
Dept_Location
Write a SQL statement to list all department names along with location and also manager name if a manager has been assigned to the department. Note that some departments still do not have a manager. Is the following correct?
编写 SQL 语句以列出所有部门名称以及位置和经理姓名（如果已将经理分配给该部门）。请注意，有些部门仍然没有经理。以下是否正确？
SELECT Dept_name, Dept_Location, emp_name AS Mgr_name
FROM Dept
LEFT JOIN Employee ON (Dept.Dept_No = Employee.Dept_No AND mgr_emp_id = emp_id)
Can this be achieved without join too? If yes, how?
这也可以在没有加入的情况下实现吗？如果是，如何？
回答by zedfoxus
Yes, you can achieve that without a JOIN, even though I prefer using JOINs. Your query will have to be modified. Here's how...
是的，即使我更喜欢使用 JOIN，您也可以在没有 JOIN 的情况下实现这一点。您的查询将必须修改。就是这样...
Example
例子
http://sqlfiddle.com/#!2/596b45/4(MySQL 5.5)
http://sqlfiddle.com/#!2/596b45/4(MySQL 5.5)
http://sqlfiddle.com/#!3/bbad4/1(SQL Server 2008)
http://sqlfiddle.com/#!3/bbad4/1(SQL Server 2008)
http://sqlfiddle.com/#!12/bbad4/1(PostgreSQL 9.2)
http://sqlfiddle.com/#!12/bbad4/1(PostgreSQL 9.2)
Assumption
假设
1) In the employee table: manager id + department id will be unique
1) 在员工表中：经理 ID + 部门 ID 将是唯一的
2) In the employee table: if employee Clark is ID 5 and has a manager with ID 1, there will a record in the table with ID 1
2）在employee表中：如果employee Clark是ID 5，并且有一个ID为1的经理，那么表中就会有一个ID为1的记录
3) MySQL 5.5 is being used
3) 正在使用 MySQL 5.5
Structure
结构
create table dept
(
dept_no int not null,
dept_name varchar(100) not null,
dept_location varchar(100) not null,
primary key (dept_no)
);
create table employee
(
emp_id int not null,
emp_name varchar(100) not null,
mgr_emp_id int,
dept_no int not null,
primary key (emp_id),
key employee_mgr_emp_id (mgr_emp_id),
foreign key fk_employee_dept_dept_no (dept_no) references dept (dept_no) on delete no action on update no action
);
insert into dept values
(1, 'Dept-1', 'Chicago'),
(2, 'Dept-2', 'London');
insert into employee values
(1, 'Clark Mgr', null, 1),
(2, 'Cameron Emp', 1, 1),
(3, 'Charlie Emp', 1, 1),
(4, 'Layton Emp', null, 2),
(5, 'Linda Emp', null, 2);
MySQL 5.5 query
MySQL 5.5 查询
Without JOIN
没有加入
select
list.*,
emp_id,
emp_name
from employee,
(
select
distinct
dept.dept_no,
dept.dept_name,
dept.dept_location,
employee.mgr_emp_id
from dept, employee
where
dept.dept_no = employee.dept_no
and employee.mgr_emp_id is not null
) list
where
employee.emp_id = list.mgr_emp_id;
With JOIN(although not completely identical to the above. I prefer using JOINs)
使用 JOIN（虽然与上面的不完全相同。我更喜欢使用JOINs）
select
list.*,
emp_id,
emp_name
from employee
inner join
(
select
distinct
dept.dept_no,
dept.dept_name,
dept.dept_location,
employee.mgr_emp_id
from dept
left join employee on dept.dept_no = employee.dept_no
where
employee.mgr_emp_id is not null
) list
on employee.emp_id = list.mgr_emp_id;
How does it work
它是如何工作的
First we want to get a list of all departments that have a manager ID of not null in the employee table. To do that, we use this query below. This query will give 2 records for Chicago because there are 2 records in employee table with a valid manager ID for Chicago's department.
首先，我们要获取员工表中经理 ID 不为空的所有部门的列表。为此，我们在下面使用此查询。此查询将提供芝加哥的 2 条记录，因为员工表中有 2 条记录具有芝加哥部门的有效经理 ID。
Without JOIN
没有加入
select
dept.dept_no,
dept.dept_name,
dept.dept_location,
employee.mgr_emp_id
from dept, employee
where
dept.dept_no = employee.dept_no
and employee.mgr_emp_id is not null;
With JOIN
加入
select
dept.dept_no,
dept.dept_name,
dept.dept_location,
employee.mgr_emp_id
from dept
left join employee on dept.dept_no = employee.dept_no
where
employee.mgr_emp_id is not null;
To get only one record, we will use distinct keyword:
为了只获取一条记录，我们将使用 distinct 关键字：
select
distinct
dept.dept_no,
...
Great, so now we know who the manager is for each department no. Let's find this person's name. To do that, we put our query in a subquery (which I nicknamed/aliased as list) and then combine it with employee table to get the desired result.
太好了，现在我们知道每个部门的经理是谁了。让我们找出这个人的名字。为此，我们将查询放在子查询中（我昵称/别名为list），然后将其与员工表结合以获得所需的结果。
