這次的任務(wù)是綜合練習(xí)：
直接上題目和代碼~
image.png
#創(chuàng)建Employee 表看疙，包含所有員工信息威酒，每個(gè)員工有其對應(yīng)的 Id, salary 和 department Id。
USE shop;
CREATE TABLE `Employee` (
`Id` INTEGER,
`name` varchar(10),
`salary` varchar(10),
`department_id` INTEGER ,
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
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);
#創(chuàng)建Department 表搔啊，包含公司所有部門的信息。
CREATE TABLE `Department` (
`Id` INTEGER,
`name` varchar(10),
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
insert into `Department`
(`Id`,`name`)
values
(1, 'IT'),
(2, 'Sales');
#編寫一個(gè) SQL 查詢，找出每個(gè)部門工資最高的員工
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;
結(jié)果
image.png
image.png
image.png
#創(chuàng)建seat 表
CREATE TABLE `seat` (
`Id` INTEGER,
`student` varchar(10),
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
insert into `seat`
(`Id`,`student`)
values
(1, 'Abbot'),
(2, 'Doris'),
(3, 'Emerson'),
(4, 'Green'),
(5, 'Jeames')
;
#其實(shí)改變的不是student迅脐，而是id愁溜，其中我使用了標(biāo)量子查詢添加了新的一列max_id, 通過取余數(shù)判斷奇數(shù)和偶數(shù)疾嗅，并使用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;
結(jié)果
image.png
image.png
image.png
#創(chuàng)建seat 表
CREATE TABLE `score` (
`Id` INTEGER,
`score` float,
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
insert into `score`
(`Id`,`score`)
values
(1, 3.50),
(2, 3.65),
(3, 4.00),
(4, 3.85),
(5, 4.00),
(6, 3.65)
;
##這個(gè)應(yīng)該考察的是dense_rank()
select score,
dense_rank() over (order by score desc) as rank1
from score;
結(jié)果：
image.png
image.png
## 創(chuàng)建ConsecutiveNums表
CREATE TABLE `ConsecutiveNums` (
`Id` INTEGER,
`num` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
insert into `ConsecutiveNums`
(`Id`,`num`)
values
(1, 1),
(2, 1),
(3, 1),
(4, 2),
(5, 1),
(6, 2),
(7, 2)
;
## 解題思路：利用移動(dòng)窗口 祝谚、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;
結(jié)果：
image.png
image.png
image.png
解題思路是：利用case when 判斷 root/inner/leaf 三種類型次泽，其中需要用到關(guān)聯(lián)子查詢以及謂詞in
## 樹節(jié)點(diǎn)，根據(jù)節(jié)點(diǎn)標(biāo)識給出節(jié)點(diǎn)的類型
## 創(chuàng)建tree表
CREATE TABLE `tree` (
`Id` INTEGER,
`p_id` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
insert into `tree`
(`Id`,`p_id`)
values
(1, null),
(2, 1),
(3, 1),
(4, 2),
(5, 2)
;
## 解題思路是：利用case when 判斷 root/inner/leaf 三種類型席爽，其中需要用到關(guān)聯(lián)子查詢以及謂詞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;
結(jié)果：
image.png
image.png
利用謂詞和窗口函數(shù)
## 創(chuàng)建employee_2表
CREATE TABLE `employee_2` (
`Id` INTEGER,
`Name` varchar(10),
`Department` varchar(10),
`ManagerId` INTEGER,
PRIMARY KEY (`Id`)
);
# 插入數(shù)據(jù)
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個(gè)下屬的主管意荤。利用謂詞和窗口函數(shù)
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);
結(jié)果：
image.png
image.png
考察的是rank()
select score,
rank() over (order by score desc) as rank1
from score;
結(jié)果：
image.png
image.png
## 創(chuàng)建question表
CREATE TABLE `question` (
`uid` INTEGER,
`action` varchar(10),
`question_id` INTEGER,
`answer_id` INTEGER,
`q_num` integer,
`timestamp` integer
);
# 插入數(shù)據(jù)
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
## 正常計(jì)算即可
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
結(jié)果：
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
結(jié)果：
image.png
拓展：若要選出玖像，前n個(gè)，就最后一行的rank <= n即可齐饮。
image.png
## 創(chuàng)建point_2d表
CREATE TABLE `point_2d` (
`x` INTEGER,
`y` INTEGER
);
# 插入數(shù)據(jù)
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捐寥，當(dāng)序號不同時(shí)，把列取出來祖驱。它自然就是笛卡爾積握恳。
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
