练习一: 各部门工资最高的员工(难度:中等)
创建Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
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);
创建Department 表,包含公司所有部门的信息。
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');
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
SELECT
d.Departmentid,
d.Employee,
d.salary
FROM
(
SELECT
d.`name` AS Departmentid,
e.`name` AS Employee,
e.salary,
row_number ()over( PARTITION BY d.`name` ORDER BY salary DESC ) rank1
FROM
Employee e
LEFT JOIN department d ON e.DepartmentId = d.id
) d
WHERE
d.rank1 = 1;
练习二: 换座位(难度:中等)
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的id是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
请创建如下所示seat表:
DROP TABLE if exists seat;
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');
假设数据输入的是上表,则输出结果如下:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
MySQL 仅8.0及以上版本支持开窗函数row_number()over()
SELECT
seat_d.rank1 id,
seat.student
FROM
seat
RIGHT JOIN (
SELECT
IF
(
id % 2 = 0,
id - 1,
IF
( id =( SELECT max( id ) FROM seat ), id, id + 1 )) id,
row_number () over () as rank1
FROM
seat
) seat_d ON seat.id = seat_d.id;
练习三: 分数排名(难度:中等)
编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
# 创建以下score表:
DROP TABLE if exists score3;
CREATE TABLE `score3` (
`ID` varchar(255) DEFAULT NULL,
`SCORE` float(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO score3 VALUES(1,3.50);
INSERT INTO score3 VALUES(2,3.65);
INSERT INTO score3 VALUES(3,4.00);
INSERT INTO score3 VALUES(4,3.85);
INSERT INTO score3 VALUES(5,4.00);
INSERT INTO score3 VALUES(6,3.65);
例如,根据上述给定的 score3 表,你的查询应该返回(按分数从高到低排列):
+-------+------+
| Score | Rank |
+-------+------+
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
+-------+------+
SELECT
score,
dense_rank () over ( ORDER BY score DESC ) `Rank`
FROM
score3;
练习四:连续出现的数字(难度:中等)
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
# 连续出现的数字
Create table If Not Exists Logs (Id int,Num int);
Truncate table Logs;
insert into Logs (Id, Num) values ('1','1');
insert into Logs (Id, Num) values ('2','1');
insert into Logs (Id, Num) values ('3', '1');
insert into Logs (Id, Num) values ('4','2');
insert into Logs (Id, Num) values ('5','1');
insert into Logs (Id, Num) values ('6','2');
insert into Logs (Id, Num) values ('7','2');
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
SELECT
l1.num AS ConsecutiveNums
FROM
LOGS l1
LEFT JOIN LOGS l2 ON l1.id = l2.id - 1
LEFT JOIN LOGS l3 ON l2.id = l3.id - 1
WHERE
l1.num = l2.num
AND l2.num = l3.num
相关内容
Kettle实现循环功能!!!
https://blog.csdn.net/weixin_43932609/article/details/119608497
ETL工具kettle的计算方式
https://blog.csdn.net/weixin_43932609/article/details/110371110
Kettle工具中Rest client组件的用法!!
https://blog.csdn.net/weixin_43932609/article/details/109988783
ETL工具kettle的Excel合并操作
https://blog.csdn.net/weixin_43932609/article/details/109326043
ETL工具kettle的插入更新操作
https://blog.csdn.net/weixin_43932609/article/details/109065366
ETL工具Kettle的发送邮件功能:
https://blog.csdn.net/weixin_43932609/article/details/108766325
ETL工具Kettle的列转行组件:
https://blog.csdn.net/weixin_43932609/article/details/108795244
ETL工具Kettle性能优化:
https://blog.csdn.net/weixin_43932609/article/details/108749304
=========================================================
人生得意须尽欢,莫使金樽空对月!
__一个热爱说唱的程序员。
今日份推荐音乐:Round_2《Kill The Game》
=========================================================