-
Employee 表包含所有员工和他们的经理。每个员工都有一个 Id,并且还有一列是经理的 Id。
-
+------+----------+-----------+----------+ |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 | +------+----------+-----------+----------+
-
-
给定 Employee 表,请编写一个SQL查询来查找至少有5名直接下属的经理。对于上表,您的SQL查询应该返回:
-
+-------+ | Name | +-------+ | John | +-------+
-
-
注意:
没有人是自己的下属。 -
解答
-
select e.name FROM employee e INNER JOIN ( select managerID,COUNT(1) as count from employee GROUP BY managerID ) t on e.Id = t.managerID AND t.count>=5
-
-
知识点总结
- 内连接的使用
- count()函数的使用
571. 给定数字的频率查询中位数(困难)
-
Numbers
表:-
+-------------+------+ | Column Name | Type | +-------------+------+ | num | int | | frequency | int | +-------------+------+ num 是这张表的主键。这张表的每一行表示某个数字在该数据库中的出现频率。
-
-
中位数 是将数据样本中半数较高值和半数较低值分隔开的值。
-
编写一个 SQL 查询,解压
Numbers
表,报告数据库中所有数字的 中位数 。结果四舍五入至 一位小数 。 -
查询结果如下例所示。
-
输入: Numbers 表: +-----+-----------+ | num | frequency | +-----+-----------+ | 0 | 7 | | 1 | 1 | | 2 | 3 | | 3 | 1 | +-----+-----------+ 输出: +--------+ | median | +--------+ | 0.0 | +--------+ 解释: 如果解压这个 Numbers 表,可以得到 [0, 0, 0, 0, 0, 0, 0, 1, 2, 2, 2, 3] ,所以中位数是 (0 + 0) / 2 = 0 。
-
-
解答
-
select round(avg(num),1) as median from (select a.*, sum(frequency) over(order by num) as rnk1, sum(frequency) over(order by num desc) as rnk2, sum(frequency) over() as s from Numbers a) tmp where rnk1>=s/2 and rnk2>=s/2
-