1.排名问题——成绩排名
-- 删除表格
DROP TABLE t_score;
-- 创建表格
CREATE TABLE t_score (
student_name VARCHAR2(50),
course_name VARCHAR2(50),
score NUMBER
);
-- 插入数据
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '数学', 85);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '英语', 78);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小明', '物理', 92);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小红', '数学', 90);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小红', '英语', 80);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '数学', 90);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '数学', 60);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '英语', 85);
INSERT INTO t_score (student_name, course_name, score) VALUES ('小李', '物理', 85);
-- 查询数据
SELECT * FROM t_score;
-- todo 实现
-- 获取每个学员按照成绩的排名
select
t1.*,
row_number() over (partition by student_name order by score desc) rn
from t_score t1;
2.排名问题——去最大最小求平均值
-- 创建表格
create table t_salary_table
(
employee_id NUMBER,
department_id NUMBER,
salary NUMBER
);
-- 插入数据
-- 插入数据
insert into t_salary_table (employee_id, department_id, salary)
values (1, 1, 50000);
insert into t_salary_table (employee_id, department_id, salary)
values (2, 1, 52000);
insert into t_salary_table (employee_id, department_id, salary)
values (3, 1, 48000);
insert into t_salary_table (employee_id, department_id, salary)
values (4, 1, 51000);
insert into t_salary_table (employee_id, department_id, salary)
values (5, 1, 49000);
insert into t_salary_table (employee_id, department_id, salary)
values (6, 2, 60000);
insert into t_salary_table (employee_id, department_id, salary)
values (7, 2, 58000);
insert into t_salary_table (employee_id, department_id, salary)
values (8, 2, 62000);
insert into t_salary_table (employee_id, department_id, salary)
values (9, 2, 59000);
insert into t_salary_table (employee_id, department_id, salary)
values (10, 2, 61000);
-- 查询数据
select *
from t_salary_table;
-- todo 实现
-- 查询每个部门去除最高、最低薪水后的平均薪水
with t1 as (
select t_salary_table.*,
row_number() over (partition by department_id order by salary asc) rn2,
row_number() over (partition by department_id order by salary desc) rn1
from t_salary_table)
select department_id, avg(salary)
from t1
where rn1 > 1
and rn2 > 1
group by department_id;
3.排名问题——去最大最小求平均分
drop table T_SCORE;
-- 创建表格
CREATE TABLE t_score (
student_id NUMBER,
score NUMBER
);
-- 插入数据
INSERT INTO t_score (student_id, score) VALUES (1, 85);
INSERT INTO t_score (student_id, score) VALUES (2, 78);
INSERT INTO t_score (student_id, score) VALUES (3, 92);
INSERT INTO t_score (student_id, score) VALUES (4, 90);
INSERT INTO t_score (student_id, score) VALUES (5, 80);
INSERT INTO t_score (student_id, score) VALUES (6, 88);
commit;
-- 查询数据
SELECT * FROM t_score;
-- todo 实现
-- 查询去除最高分、最低分后的平均分数
with t1 as (
select t_score.*,
row_number() over (order by score asc) rn2,
row_number() over (order by score desc) rn1
from t_score)
select avg(score)
from t1
where rn1 > 1
and rn2 > 1;
4.TOP-N-查询前三名成绩
-- 删除表格
drop table t_score;
-- 创建表格
create table t_score
(
course_id NUMBER,
student_id NUMBER,
score NUMBER
);
-- 插入数据
insert into t_score
values (1, 1, 85);
insert into t_score
values (1, 2, 78);
insert into t_score
values (1, 3, 92);
insert into t_score
values (1, 4, 90);
insert into t_score
values (1, 5, 80);
insert into t_score
values (1, 6, 92);
insert into t_score
values (1, 7, 78);
insert into t_score
values (1, 8, 92);
insert into t_score
values (1, 9, 85);
insert into t_score
values (2, 1, 88);
insert into t_score
values (2, 2, 82);
insert into t_score
values (2, 3, 90);
insert into t_score
values (2, 4, 85);
insert into t_score
values (2, 5, 78);
insert into t_score
values (2, 6, 88);
insert into t_score
values (2, 7, 82);
insert into t_score
values (2, 8, 90);
insert into t_score
values (2, 9, 82);
commit;
-- 查询数据
select *
from t_score;
-- todo 题目要求: “成绩表”中记录了学生选修的课程号、学生的学号,以及对应课程的成绩。为了对学生成绩进行考核,现需要查询每门课程前三名学生的成绩。
-- todo 注意:如果出现同样的成绩,则视为同一个名次
with t2 as (
select t1.*,
dense_rank() over (partition by course_id order by score desc) rn
from t_score t1)
select
*
from t2
where t2.rn <= 3;
5.TOP-N-查询前二名工资
-- 删除表格
DROP TABLE t_employee;
-- 创建表格
CREATE TABLE t_employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
department_id NUMBER
);
-- 插入数据
INSERT INTO t_employee (emp_id, emp_name, salary, department_id)
SELECT 1, '小明', 50000, 1 FROM dual UNION ALL
SELECT 2, '小红', 52000, 1 FROM dual UNION ALL
SELECT 3, '小李', 48000, 1 FROM dual UNION ALL
SELECT 4, '小张', 60000, 1 FROM dual UNION ALL
SELECT 5, '小王', 58000, 1 FROM dual UNION ALL
SELECT 6, '小刚', 62000, 1 FROM dual UNION ALL
SELECT 7, '小丽', 45000, 2 FROM dual UNION ALL
SELECT 8, '小芳', 47000, 2 FROM dual UNION ALL
SELECT 9, '小晓', 49000, 2 FROM dual UNION ALL
SELECT 10, '小华', 52000, 2 FROM dual UNION ALL
SELECT 11, '小雷', 52000, 2 FROM dual;
COMMIT;
-- 查询数据
SELECT * FROM t_employee;
-- todo 实现
with t2 as (
select t1.*,
dense_rank() over (partition by department_id order by salary desc) rn
from t_employee t1)
select
*
from t2
where t2.rn <= 2;
6.累计问题-员工的累计薪资
-- 查询员工的累计薪水
drop table t_employee;
create table t_employee
(
id int,
month int,
salary int
);
truncate table t_employee;
insert into t_employee
values ('1', '1', '20');
insert into t_employee
values ('2', '1', '20');
insert into t_employee
values ('1', '2', '30');
insert into t_employee
values ('2', '2', '30');
insert into t_employee
values ('3', '2', '40');
insert into t_employee
values ('1', '3', '40');
insert into t_employee
values ('3', '3', '60');
insert into t_employee
values ('1', '4', '60');
insert into t_employee
values ('3', '4', '70');
insert into t_employee
values ('1', '7', '90');
insert into t_employee
values ('1', '8', '90');
commit;
select *
from t_employee
order by id, month desc;
-- 编写一个解决方案,在一个统一的表中计算出每个员工的 累计工资汇总 。
--
-- 员工的 累计工资汇总 可以计算如下:
-- 对于该员工工作的每个月,将 该月 和 前两个月 的工资 加 起来。这是他们当月的 3 个月总工资和 。如果员工在前几个月没有为公司工作,那么他们在前几个月的有效工资为 0 。
-- 不要 在摘要中包括员工 最近一个月 的 3 个月总工资和。
-- 不要 包括雇员 没有工作 的任何一个月的 3 个月总工资和。
-- 返回按 id 升序排序 的结果表。如果 id 相等,请按 month 降序排序。
select
t1.*,
sum(salary)
over (partition by id order by month desc range between current row and 2 following) as salary
from t_employee t1
order by id asc, month desc;
7. 组内比较文件-每组大于平均值
drop table t_score;
-- 创建表格
create table t_score
(
student_name VARCHAR2(20),
course_name VARCHAR2(20),
score NUMBER(3)
);
-- 插入数据
insert into t_score
values ('张三', '语文', 90);
insert into t_score
values ('李四', '语文', 81);
insert into t_score
values ('王朝', '语文', 79);
insert into t_score
values ('马汉', '语文', 88);
insert into t_score
values ('张三', '数学', 85);
insert into t_score
values ('李四', '数学', 86);
insert into t_score
values ('王朝', '数学', 92);
insert into t_score
values ('马汉', '数学', 83);
insert into t_score
values ('张三', '英语', 87);
insert into t_score
values ('李四', '英语', 98);
insert into t_score
values ('王朝', '英语', 93);
insert into t_score
values ('马汉', '英语', 95);
commit;
-- 查询数据
select *
from t_score;
-- todo “成绩表”,记录了每个学生各科的成绩。现在要查找单科成绩高于该科目平均成绩的学生名单。
select * from
(select t1.*, avg(score) over (partition by course_name) as avg_score
from t_score t1)
where score>avg_score;
8. 组内比较问题-低于平均薪水的雇员
drop table t_employee;
-- 创建员工表
create table t_employee
(
employeeID INT,
departmentID INT,
salary INT
);
-- 插入数据
insert into t_employee (employeeID, departmentID, salary)
values (10001, 1, 60117);
insert into t_employee (employeeID, departmentID, salary)
values (10002, 2, 92102);
insert into t_employee (employeeID, departmentID, salary)
values (10003, 2, 86074);
insert into t_employee (employeeID, departmentID, salary)
values (10004, 1, 66596);
insert into t_employee (employeeID, departmentID, salary)
values (10005, 1, 66961);
insert into t_employee (employeeID, departmentID, salary)
values (10006, 2, 81046);
insert into t_employee (employeeID, departmentID, salary)
values (10007, 2, 94333);
insert into t_employee (employeeID, departmentID, salary)
values (10008, 1, 75286);
insert into t_employee (employeeID, departmentID, salary)
values (10009, 2, 85994);
insert into t_employee (employeeID, departmentID, salary)
values (10010, 1, 76884);
commit;
-- todo 现在公司要找出每个部门低于平均薪水的雇员,然后进行培训来提高雇员工作效率,从而提高雇员薪水。
select * from
(select t1.*, avg(salary) over (partition by departmentID) avg_salary
from t_employee t1)
where salary<avg_salary;
9. 连续问题-连续3次为球队得分的球员名单
-- 创建分数表,并为列名增加注释
drop table t_score;
CREATE TABLE t_score (
team_name VARCHAR2(50),
player_id INT,
player_name VARCHAR2(50),
score INT,
score_time TIMESTAMP
);
COMMENT ON COLUMN t_score.team_name IS '球队名称';
COMMENT ON COLUMN t_score.player_id IS '球员ID';
COMMENT ON COLUMN t_score.player_name IS '球员姓名';
COMMENT ON COLUMN t_score.score IS '得分';
COMMENT ON COLUMN t_score.score_time IS '得分时间';
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, TO_TIMESTAMP('2023-12-25 10:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, TO_TIMESTAMP('2023-12-25 10:15:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 1, TO_TIMESTAMP('2023-12-25 10:30:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 3, '安东尼·戴维斯', 2, TO_TIMESTAMP('2023-12-25 10:32:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, TO_TIMESTAMP('2023-12-25 10:45:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 3, TO_TIMESTAMP('2023-12-25 11:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 2, TO_TIMESTAMP('2023-12-25 11:15:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('洛杉矶湖人队', 23, '勒布朗·詹姆斯', 2, TO_TIMESTAMP('2023-12-25 11:30:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 1, TO_TIMESTAMP('2023-12-25 10:10:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 1, TO_TIMESTAMP('2023-12-25 10:25:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 1, TO_TIMESTAMP('2023-12-25 10:40:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 11, '克莱·汤普森', 2, TO_TIMESTAMP('2023-12-25 10:45:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 2, TO_TIMESTAMP('2023-12-25 10:55:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 2, TO_TIMESTAMP('2023-12-25 11:10:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 3, TO_TIMESTAMP('2023-12-25 11:25:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 3, TO_TIMESTAMP('2023-12-25 11:40:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO t_score VALUES ('金州勇士队', 30, '斯蒂芬·库里', 3, TO_TIMESTAMP('2023-12-25 11:55:00', 'YYYY-MM-DD HH24:MI:SS'));
commit;
-- 查询数据
SELECT * FROM t_score;
-- todo 获取连续三次为球队得分的球员
with t1 as (
select
t_score.*,
lead(player_id,1) over (partition by team_name order by score_time) rn1,
lead(player_id,2) over (partition by team_name order by score_time) rn2
from t_score)
select distinct player_id,player_name,team_name from t1 where t1.player_id=t1.rn1 and t1.rn1=t1.rn2;
-- todo 等差数列的方法
with t1 as (
select ROWNUM id, t_score.*
from t_score
),
t2 as (
select id,team_name,player_name,player_id,
id - row_number() over (partition by team_name,player_name order by score_time) 差值
from t1),
t3 as (
select team_name,player_name,count(*) over (partition by team_name,player_name,差值) 计数 from t2)
select distinct team_name,player_name from t3 where 计数>=3;
10. 连续问题-连续空余座位
create table Cinema
(
seat_id number primary key,
free varchar(2)
);
insert into Cinema (seat_id, free)
values ('1', '1');
insert into Cinema (seat_id, free)
values ('2', '0');
insert into Cinema (seat_id, free)
values ('3', '1');
insert into Cinema (seat_id, free)
values ('4', '1');
insert into Cinema (seat_id, free)
values ('5', '1');
select *
from cinema;
commit;
-- todo 查找电影院所有连续可用的座位。
-- todo 返回按 seat_id 升序排序 的结果表。
-- todo 测试用例的生成使得两个以上的座位连续可用
-- todo 方式1 lad lead
with t1 as (
select Cinema.*,
lead(free, 1) over (order by seat_id) rn1,
lag(free, 1) over (order by seat_id) rn2
from Cinema)
select seat_id
from t1
where (t1.free = 1 and t1.rn1 = 1)
or (t1.rn1 is null and t1.rn2 = 1 and t1.free=1);
-- todo 方式2 等差数列
with t1 as(
select
Cinema.*,
row_number() over (partition by free order by seat_id) as rn1,
seat_id - (row_number() over (partition by free order by seat_id)) as 差值
from Cinema),
t2 as (
select seat_id,count(差值) over (partition by 差值) as 计数 from t1 where free=1)
select seat_id from t2 where 计数>1 order by seat_id;
11. 连续问题-连续出现的数字
Create table 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');
commit;
select * from logs;
-- todo 需求: 找出所有至少连续出现三次的数字。
-- todo 返回的结果表中的数据可以按 任意顺序 排列。
-- todo 方式1 等差数列
with t1 as (
select
Logs.*,
id - (row_number() over (partition by num order by id)) 差值
from Logs),
t2 as (
select num,count(1) over (partition by 差值,num) as 计数 from t1)
select distinct num from t2 where 计数>=3;
-- todo 方式2 lag lead
with t1 as (
select
Logs.*,
lag(num) over (order by id) lag1,
lead(num) over (order by id) lead1
from Logs)
select distinct num from t1 where t1.num=lag1 and t1.num=lead1;