570. Managers with at Least 5 Direct Reports 至少有5个直接汇报员工的经理

2021年09月15日 阅读数:1
这篇文章主要向大家介绍570. Managers with at Least 5 Direct Reports 至少有5个直接汇报员工的经理,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.html

+------+----------+-----------+----------+
|Id    |Name 	  |Department |ManagerId |
+------+----------+-----------+----------+
|101   |John 	  |A 	      |null      |
|102   |Dan 	  |A 	      |101       |
|103   |James 	  |A 	      |101       |
|104   |Amy 	  |A 	      |101       |
|105   |Anne 	  |A 	      |101       |
|106   |Ron 	  |B 	      |101       |
+------+----------+-----------+----------+

Given the Employee table, write a SQL query that finds out managers with at least 5 direct report. For the above table, your SQL query should return:sql

+-------+
| Name  |
+-------+
| John  |
+-------+

Note:
No one would report to himself.post

给一个员工表和一个经理表,员工表里有经理id列,查询出全部至少有5个直接汇报员工的经理。url

解法1:code

SELECT
    Name
FROM
    Employee AS t1 JOIN
    (SELECT
        ManagerId
    FROM
        Employee
    GROUP BY ManagerId
    HAVING COUNT(ManagerId) >= 5) AS t2
    ON t1.Id = t2.ManagerId
;  

解法2:htm

with cte as(
select ManagerId from employee
group by ManagerId
having count(Id)>=5
) select Name from employee e join cte c on e.Id=c.ManagerId  

 

All LeetCode Questions List 题目汇总