组队学习sql之综合练习2

基础及前5题的综合练习见：组队学习sql之基础+综合练习1

MySQL Leetcode练习

Task06综合练习一共11题，都是leetcode上中等等级的练习题，需要多加练习呀。本篇实现6~8题。

570 至少有五名直接下属的经理

题目

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

+------+----------+-----------+----------+
|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个下属的主管。对于上面的表，结果应输出：

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

注意:

没有人向自己汇报。

解答

思路：没有人管的是主管，要求管5人

SELECT q.Name 
FROM (SELECT ManagerId,COUNT(Id) AS cnt FROM employee s GROUP BY ManagerId) p
JOIN employee q ON q.Id = p.ManagerId
WHERE q.ManagerId IS NULL AND p.cnt = 5;

分数排名(类似)

题目

练习三的分数表，实现排名功能，但是排名需要是非连续的，如下：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 4    |
| 3.50  | 6    |
+-------+------

解答

• 思路1 MySQL函数

SELECT Score,RANK() OVER (ORDER BY Score DESC) AS "Rank"
FROM Scores P;

• 思路2 不等值连接

SELECT t1.Score,t2.Rank
FROM Scores t1 LEFT JOIN (
SELECT P.Id,COUNT(Q.Id) + 1 AS "Rank"
FROM Scores P LEFT JOIN Scores Q 
ON P.Score < Q.Score
GROUP BY P.Id
) t2 
ON t1.Id = t2.Id
ORDER BY t1.Score Desc;

578 查询回答率最高的问题

题目

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

写一条sql语句找出回答率最高的问题。

举例：

输入

uid	action	question_id	answer_id	q_num	timestamp
5	show	285	null	1	123
5	answer	285	124124	1	124
5	show	369	null	2	125
5	skip	369	null	2	126

输出

survey_log
285

说明

问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。

**注意：**最高回答率的意思是：同一个问题出现的次数中回答的比例。

解答

思路 连接的字段是sv【回答率】，sv=MAX(sv)

SELECT question_id AS 'survey_log'
FROM (SELECT question_id,SUM(CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(1) AS sv
FROM survey_log 
GROUP BY question_id) t2
WHERE t2.sv =
(SELECT MAX(sv)
FROM
(SELECT question_id,SUM(CASE WHEN answer_id IS NOT NULL THEN 1 ELSE 0 END)/COUNT(1) AS sv
FROM survey_log 
GROUP BY question_id) t);

未完待续