SQL案例-交叉连接，消除笛卡尔积

编写一个SQL查询，查询至少有5名直接下属的经理，以任意顺序返回结果表。

drop table Employee;

Create table If Not Exists Employee (id int, name varchar(255), department varchar(255), managerId int);

Truncate table Employee;

insert into Employee (id, name, department, managerId) values ('101', 'John', 'A', NULL);

insert into Employee (id, name, department, managerId) values ('102', 'Dan', 'A', '101');

insert into Employee (id, name, department, managerId) values ('103', 'James', 'A', '101');

insert into Employee (id, name, department, managerId) values ('104', 'Amy', 'A', '101');

insert into Employee (id, name, department, managerId) values ('105', 'Anne', 'A', '101');

insert into Employee (id, name, department, managerId) values ('106', 'Ron', 'B', '101');

思路101>102,103,104,105,106五个下属
交叉连接
笛卡尔积，添加where的消除笛卡尔积的方式，去除多余的影响
去除重复的id对应的经理

select
    distinct
                     F.managerId,
                     E.name,
                     count(E.id)  over() as num
from
     Employee E ,Employee F
where E.id=F.managerId  ;