SQL - 8

11.至少有5名直接下属的经理

需求：Employee 表，请编写一个SQL查询来查找至少有5名直接下属的经理。

结果展示：

Name

John

建表语句:

Create table If Not Exists Employee (Id int, Name varchar(255), Department varchar(255), ManagerId int);
Truncate table 14_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);

方法1：

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：

select
	Name
from
	Employee
where Id in (
			select
    			  ManagerId
			from
    			  Employee
			group by
    			  ManagerId
			having
    			  count(*)>=5 );

12.给定数字的频率查询中位数

需求:需求：请编写一个查询来查找所有数字的中位数并将结果命名为 median 。

结果展示：

median

2.0000

建表语句：

Create table If Not Exists Numbers (Number int, Frequency int);
Truncate table Numbers;
insert into Numbers (Number, Frequency) values (0, 2);
insert into Numbers (Number, Frequency) values (1, 1);
insert into Numbers (Number, Frequency) values (2, 3);
insert into Numbers (Number, Frequency) values (3, 1);

方法1：

select
      avg(t.number) as median
from
      (select
             n1.number,
             n1.frequency,
             (select 
                   sum(frequency) 
              from 
                   Numbers n2
              where 
                   n2.number<=n1.number
             ) as asc_frequency,
             (select
                   sum(frequency)
              from 
                   Numbers n3 
              where 
                   n3.number>=n1.number
             ) as desc_frequency
      from 
             Numbers n1
      ) t
where 
      t.asc_frequency>= (select sum(frequency) from Numbers)/2
      and t.desc_frequency>= (select sum(frequency) from Numbers)/2;