Oracle基础3

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;