这次的任务是综合练习：
直接上题目和代码~
image.png
#创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。
USE shop;
CREATE TABLE `Employee` (
`Id` INTEGER,
`name` varchar(10),
`salary` varchar(10),
`department_id` INTEGER ,
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `Employee`
(`Id`,`name`,`salary`,`department_id`)
values
(1,'Joe','7000',1),
(2,'Henry','8000',2),
(3,'Sam','6000',2),
(4,'Max','9000',1);
#创建Department 表，包含公司所有部门的信息。
CREATE TABLE `Department` (
`Id` INTEGER,
`name` varchar(10),
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `Department`
(`Id`,`name`)
values
(1, 'IT'),
(2, 'Sales');
#编写一个 SQL 查询，找出每个部门工资最高的员工
select
*
from
(select
*
from(
select name,
salary,
department_id,
rank() over (partition by department_id order by salary desc) as rank_salary
from Employee)table1
where rank_salary = 1)table2
left join
(select *
from Department)table3
on table3.Id = table2.department_id;
结果
image.png
image.png
image.png
#创建seat 表
CREATE TABLE `seat` (
`Id` INTEGER,
`student` varchar(10),
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `seat`
(`Id`,`student`)
values
(1, 'Abbot'),
(2, 'Doris'),
(3, 'Emerson'),
(4, 'Green'),
(5, 'Jeames')
;
#其实改变的不是student，而是id，其中我使用了标量子查询添加了新的一列max_id, 通过取余数判断奇数和偶数，并使用case when then else end语句来写条件改变id，最后再order by id就可以了~
select
case when t1.id < t1.max_id and t1.id%2 = 1 then t1.id+1
when t1.id < t1.max_id and t1.id%2 = 0 then t1.id-1
when t1.id = t1.max_id and t1.id%2 = 1 then t1.id
when t1.id = t1.max_id and t1.id%2 = 0 then t1.id-1
else null
end as id,
student
from (
select *,
(select max(id) from seat) as max_id
from seat
)t1
order by id;
结果
image.png
image.png
image.png
#创建seat 表
CREATE TABLE `score` (
`Id` INTEGER,
`score` float,
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `score`
(`Id`,`score`)
values
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65)
;
##这个应该考察的是dense_rank()
select score,
dense_rank() over (order by score desc) as rank1
from score;
结果：
image.png
image.png
## 创建ConsecutiveNums表
CREATE TABLE `ConsecutiveNums` (
`Id` INTEGER,
`num` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `ConsecutiveNums`
(`Id`,`num`)
values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2)
;
## 解题思路：利用移动窗口 、case when、count、min、max
select
case when
count(num) over (order by id rows between 0 preceding and 2 following) = 3 and
min(num) over(order by id rows between 0 preceding and 2 following) = max(num) over(order by id rows between 0 preceding and 2 following)
then num
end as ConsecutiveNums
from ConsecutiveNums;
结果：
image.png
image.png
image.png
解题思路是：利用case when 判断 root/inner/leaf 三种类型，其中需要用到关联子查询以及谓词in
## 树节点，根据节点标识给出节点的类型
## 创建tree表
CREATE TABLE `tree` (
`Id` INTEGER,
`p_id` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `tree`
(`Id`,`p_id`)
values
(1, null),
(2, 1),
(3, 1),
(4, 2),
(5, 2)
;
## 解题思路是：利用case when 判断 root/inner/leaf 三种类型，其中需要用到关联子查询以及谓词in
select
id,
case when p_id is null then 'Root'
when p_id is not null and id in (select distinct(p_id) from tree) then 'Inner' else 'Leaf' end as tree_Type
from tree;
结果：
image.png
image.png
利用谓词和窗口函数
## 创建employee_2表
CREATE TABLE `employee_2` (
`Id` INTEGER,
`Name` varchar(10),
`Department` varchar(10),
`ManagerId` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入数据
insert into `employee_2`
(`Id`,`Name`,`Department`,`ManagerId`)
values
(101, 'John', 'A', null),
(102, 'Dan', 'A', 101),
(103, 'James', 'A', 101),
(104, 'Amy', 'A', 101),
(105, 'Anne', 'A', 101),
(106, 'Ron', 'B', 101)
;
###找出有5个下属的主管。利用谓词和窗口函数
select
name
from
employee_2
where id in (select distinct
case when count(ManagerId) over (partition by ManagerId) = 5 then ManagerId else null end as ManagerId
from employee_2);
结果：
image.png
image.png
考察的是rank()
select score,
rank() over (order by score desc) as rank1
from score;
结果：
image.png
image.png
## 创建question表
CREATE TABLE `question` (
`uid` INTEGER,
`action` varchar(10),
`question_id` INTEGER,
`answer_id` INTEGER,
`q_num` integer,
`timestamp` integer
);
# 插入数据
insert into `question`
(`uid`,`action`,`question_id`,`answer_id`,`q_num`,`timestamp`)
values
(5, 'show', 285, null, 1, 123),
(5, 'answer', 285, 124124, 1, 124),
(5, 'show', 369, null, 2, 125),
(5, 'skip', 369, null, 2, 126)
;
select * from question;
image.png
## 正常计算即可
select
t1.question_id,
max(t1.answer_rate) as answer_rate
from(
select
question_id,
count(answer_id)/count(question_id) as answer_rate
from question
group by question_id)t1
结果：
image.png
image.png
select * from employee;
insert into `employee` values (5,'Janet','69000',1),(6,'Randy','85000',1);
select * from employee;
image.png
解法如下：利用rank() over( partition )先分组排序，left join加上部门信息，之后再选出rank <= 3
select
t2.name as department,
t1.name,
t1.salary
from(
select
Department_id,
Salary,
Name,
rank() over (partition by Department_id order by Salary desc) as rank1
from employee)t1
left join
(select *
from Department)t2
on t2.Id = t1.department_id
where t1.rank1 <= 3
结果：
image.png
拓展：若要选出，前n个，就最后一行的rank <= n即可。
image.png
## 创建point_2d表
CREATE TABLE `point_2d` (
`x` INTEGER,
`y` INTEGER
);
# 插入数据
insert into `point_2d`
(`x`,`y`)
values
(-1, -1),
(0,0),
(-1, -2)
;
先加入序号
select
row_number() over(order by x) as id,
x,
y
from point_2d;
image.png
用此表自身两次select * from t1,t2，当序号不同时，把列取出来。它自然就是笛卡尔积。
select
t1.id as id,
t1.x as x1,
t1.y as y1,
t2.x as x2,
t2.y as y2
from
(select
row_number() over(order by x) as id,
x,
y
from point_2d) t1, (select
row_number() over(order by x) as id,
x,
y
from point_2d) t2
where t1.id <> t2.id;
image.png
select
t1.id as id,
t1.x as x1,
t1.y as y1,
t2.x as x2,
t2.y as y2,
MIN(SQRT(POW((t1.x-t2.x),2)+POW((t1.y-t2.y),2))) shortest
from
(select
row_number() over(order by x) as id,
x,
y
from point_2d) t1, (select
row_number() over(order by x) as id,
x,
y
from point_2d) t2
where t1.id <> t2.id;
image.png
