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;
作者:yuexiuping
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.