SQL自连接与空值

Question

我将所有员工(经理和员工)都放在一个名为Employee的表格中。表格如下所示：

表格

+-------+------------+---------+---------+------------+
|emp_id | name       | dept_id | salary  | manager_id |
+=======+============+=========+=========+============+
|  1    | Sally      |   1     | 20000   |  null      |
|  2    | Ajit       |   2     | 20000   |   1        |
|  3    | Rahul      |   1     | 20000   |   1        |
|  4    | uday       |   1     | 20000   |   null     |
|  5    | john       |   1     | 20000   |   null     |
|  6    | netaji     |   2     | 20000   |    2       |
|  7    | prakriti   |   3     | 1111    |    3       |
|  8    | sachin     |   3     | 1111    |    3       |
|  9    | santosh    |   1     | 1111    |    2       |
|  10   | Ravi       |   1     | 1111    |    2       |
+-------+------------+---------+---------+------------+

经理和员工属于同一张表。manager_id指代的是作为经理的emp_id。

我想编写查询语句，以计算属于每位经理的员工数量。因此，即使某个经理没有任何下属员工，计数也将显示为0。

期望输出结果如下：

+------+----------+
|Count |  Manager |
+======+==========+
| 2    |  Sally   |
| 3    |  Ajit    |
| 2    |  Rahul   |
| 0    |  Uday    |
| 0    |  John    |
+------+----------+

- Rahul Borkar

---

你怎么知道谁是经理，谁不是？我不清楚输出是如何从输入中得出的。 - user2864740

emp_id 在 manager_id 列中的员工是经理。 - Rahul Borkar

你怎么知道John是经理，因为没有人为他工作？ - user2864740

因为他的manager_id字段为空，而除经理以外的每个员工都属于一个经理。 - Rahul Borkar

为什么Uday和John也是经理？ - John Woo

因为他们的manager_id字段中有null。Ajit、Rahul成为经理的原因是他们的emp_id在其他人的manager_id字段中。 - Rahul Borkar

Answer 1

您需要在表格上进行左自连接。左连接将确保每个经理都有一行，即使他们下面没有员工。您需要在连接的员工侧上使用COUNT()聚合函数，如果经理没有下属，则该字段将为NULL。COUNT()实际上不计算NULL，因此这应该会在您需要它们的地方产生零。

此查询中的WHERE子句通过查看其manager_id是否为NULL或在加入的表中是否有任何匹配项来定义经理，这意味着有人将他们设置为经理。

SELECT mgr.name, COUNT(emp.emp_id) AS employee_count
FROM Employee AS mgr
LEFT JOIN Employee AS emp ON emp.manager_id=mgr.emp_id
WHERE mgr.manager_id IS NULL OR emp.emp_id IS NOT NULL
GROUP BY mgr.name

- Mike D.

Answer 2

正确的解决方案可能涉及到修复方案，因为任何方法都会对“子经理”（由其他人管理且具有manager_id的人）失败，但目前没有管理任何人。

无论如何，如果上述限制是可接受的，那么人们就是经理，如果：

• 他们有一个NULL manager_id（如评论中所述），或者
• 他们目前管理其他员工

然后可以使用此查询（示例sqlfiddle）：

SELECT m.name as Manager, COUNT(e.id) as `Count`
FROM employee m
LEFT JOIN employee e
ON m.id = e.manager_id
GROUP BY m.id, m.name, m.manager_id
HAVING `Count` > 0 OR m.manager_id IS NULL

注释/解释：

• 这里使用了LEFT [OUTER]连接，否则将无法找到没有管理任何人的经理。然后通过HAVING子句对分组结果进行过滤。

• COUNT应用于特定列，而不是*；这样做，该列中的NULL值将不会被计算在内。在这种情况下，意味着没有匹配（e）的员工（m）不会自动被COUNT条件在HAVING中选中。（LEFT JOIN保留左侧记录，即使没有加入匹配-在这种情况下，所有右侧列都为NULL。）

• GROUP BY包含所有分组字段，即使它们看起来是多余的。例如，这允许在HAVING中使用manager_id字段。（按ID进行分组是为了防止两个经理拥有相同的名称，或在输出子句中选择它。）

- user2864740

Answer 3

这是解决方案：您需要在员工表上进行自连接。

  SELECT e1.manager_id, e2.name, COUNT (1) AS COUNT
    FROM Employee e1 JOIN Employee e2 ON e1.manager_id = e2.id
GROUP BY e1.manager_id, e2.name
UNION ALL
SELECT e3.id, e3.name, 0 AS COUNT
  FROM Employee e3
 WHERE     manager_id IS NULL
       AND e3.id NOT IN (  SELECT e1.manager_id
                             FROM    Employee e1
                                  JOIN
                                     Employee e2
                                  ON e1.manager_id = e2.id
                         GROUP BY e1.manager_id, e2.name)

- dursun

Answer 4

也许这可以帮助：

select t1.name, count(*) -- all managers with emps
  from t t1
  join t t2
    on t1.emp_id = t2.manager_id 
 group 
    by t1.name
 union
   all
select t1.name, 0  -- all managers without emps
  from t t1
  left
  join t t2
    on t1.emp_id = t2.manager_id
 where t1.manager_id is null 
   and t2.emp_id is null

- DirkNM

Answer 5

尝试以下操作：

select (select count(*) from employees b where b.manager_id = a.emp_id)) as Count, a.Name as manager from  employees a where a.emp_id in (select distict c.manager_id from employees c)

- Ronak Shah

---

这是错误的，因为它对于不是经理的员工返回0。 - Rahul Borkar

你想过滤掉不是经理的员工吗？如果是，那么如何检测员工是否为经理，因为你已经提到可能有没有下属的经理。 - Ronak Shah

我已经采用了这个逻辑：在manager_id列中，emp_id出现的员工是经理。 - Ronak Shah

我已经更新了我的答案，现在请测试一下。 - Ronak Shah

Answer 6

查询

CREATE TABLE employee(emp_id varchar(5) NOT NULL,  
emp_name varchar(20) NULL,  
dt_of_join date NULL,  
emp_supv varchar(5) NULL,  
CONSTRAINT emp_id PRIMARY KEY(emp_id) ,  
CONSTRAINT emp_supv FOREIGN KEY(emp_supv)   
REFERENCESemployee(emp_id));  

- Rinu Rathore

---

我需要一条SQL查询语句来获取记录，而不是创建表。 - Rahul Borkar

Answer 7

你需要这样做 LEFT OUTER JOIN：

SELECT movies.title,sequelies.title AS sequel_title
FROM movies
LEFT OUTER JOIN movies sequelies
    ON movies.sequel_id = sequelies.id ;

- t.h

---

你的回答对这个问题有什么新的贡献，而不是多年前已经写过的？ - Shadow