【task06作业】

关注
### 练习一: 各部门工资最高的员工（难度：中等）

use 决胜秋招

DROP TABLE if EXISTS Employee;

CREATE TABLE Employee

(id INT,

name VARCHAR(20),

salary INT,

departmentid INT,

PRIMARY KEY (id));

INSERT INTO Employee VALUES(1,'Joe',70000,1);

INSERT INTO Employee VALUES(2,'Henry',80000,2);

INSERT INTO Employee VALUES(3,'Sam',60000,2);

INSERT INTO Employee VALUES(4,'Max',90000,1);

DROP TABLE if EXISTS Department;

CREATE TABLE Department

(id INT,

name VARCHAR(20),

PRIMARY KEY (id));

INSERT INTO Department VALUES(1,'IT');

INSERT INTO Department VALUES(2,'Sales');

查询

select deptname,staffname,salary from

(select deptname,staffname,salary,rank() over (partition by deptname order by salary desc) as 

top1_or_not from

(select department.name as deptname,employee.name as staffname,salary,departmentid from employee left join department on 

employee.departmentid = department.id) A)B

where B.top1_or_not = 1;

## 练习二: 换座位（难度：中等）

CREATE TABLE seat

(id INT,

student VARCHAR(20),

PRIMARY KEY (id));

INSERT INTO seat VALUES(1,'Abbot');

INSERT INTO seat VALUES(2,'Doris');

INSERT INTO seat VALUES(3,'Emerson');

INSERT INTO seat VALUES(4,'Green');

INSERT INTO seat VALUES(5,'Jeames');

select (case when mod(id,2) = 0 then id-1

when id = (select count(*) from seat) then id

else id + 1 end) as new_id,student

from seat order by new_id;

### 练习三分数排名（难度：中等）

rank 1，1，1，4

dense rank 1，1，1，2

row_number 1，2，3，4

### 练习四连续出现的数字（难度：中等）

DROP TABLE if exists logs;

CREATE TABLE logs

(id INT,

num INT,

PRIMARY KEY (id));

INSERT INTO logs VALUES (1, 1);

INSERT INTO logs VALUES (2, 1);

INSERT INTO logs VALUES (3, 1);

INSERT INTO logs VALUES (4, 2);

INSERT INTO logs VALUES (5, 1);

INSERT INTO logs VALUES (6, 2);

INSERT INTO logs VALUES (7, 2);

with A as (select Id,num,LAG(num,1) over () as last_one,

LAG(num,2) over () as last_two 

from logs)

select num from A 

where A.num = A.last_one and A.num = A.last_two;

### 练习五树节点 （难度：中等）

DROP TABLE if exists tree;

CREATE TABLE tree

(id INT,

p_id INT,

PRIMARY KEY (id));

INSERT INTO tree VALUES (1, null);

INSERT INTO tree VALUES (2, 1);

INSERT INTO tree VALUES (3, 1);

INSERT INTO tree VALUES (4, 2);

INSERT INTO tree VALUES (5, 2);

select id,case when p_id is null then "root"

when id in (select p_id from tree) then "Inner"

else "leaf" end

from tree;

-- 练习六至少有五名直接下属的经理 

太简单，懒得写了，select ManagerId按ManagerId group by 

having count(*) = 5,

再left join 员工表 Name;

### 练习七：查询回答率最高的问题 （难度：中等）

DROP TABLE IF EXISTS survey_log;

CREATE TABLE survey_log (

uid INT,

action VARCHAR (20),

question_id INT,

answer_id INT,

q_num INT,

TIMESTAMP INT

);

INSERT INTO survey_log VALUES (5, 'show', 285, NULL, 1, 123);

INSERT INTO survey_log VALUES (5, 'answer', 285, 124124, 1, 124);

INSERT INTO survey_log VALUES (5, 'show', 369, NULL, 2, 125);

INSERT INTO survey_log VALUES (5, 'skip', 369, NULL, 2, 126);

with A as (select question_id,count(question_id)/2 as show_cmt,

sum(case when action = "answer" then 1 else 0 end) as answer

from survey_log 

group by question_id)

select question_id,answer/show_cmt as answer_rate

from A

order by answer_rate desc limit 1;

-- 练习八：各部门前3高工资的员工

INSERT INTO employee VALUES(5, 'Janet', 69000, 1);

INSERT INTO employee VALUES(6, 'Randy', 85000, 1);

select department.name as deptname,TABLEA.name as em_name,salary from

(select departmentid,name,row_number() over (partition by departmentid order by salary desc)

as rank_of_department,salary from employee) TABLEA

left join department on TABLEA.departmentid = department.id

where rank_of_department < 4;