Leetcode Database - Oracle Partition

来源：互联网发布：长江大学武汉校区知乎编辑：程序博客网时间：2024/05/16 05:14

用oracle分析函数实现的 Leetcode Database 题

第一题：Department Top Three Salaries

select a.depart, a.name, a.salaryfrom (select d.name depart, e.name name, rank()over(partition by d.id order by e.salary desc) rank, e.salary salaryfrom department d, employee ewhere d.id=e.departmentid) awhere a.rank<=3;

附：建表及数据准备

DROP TABLE Employee PURGE;CREATE TABLE Employee (      Id INT PRIMARY KEY,     Name CHAR(20), Salary INT, DepartmentId INT );INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(1,'joe',70000,1);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(2,'henry',80000,2);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(3,'sam',60000,2);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(4,'Max',90000,1);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(5,'Janet',69000,1);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(6,'Randy',85000,1);INSERT INTO Employee(Id, Name,Salary,DepartmentId) VALUES(7,'rap',85000,1);DROP TABLE  Department PURGE; CREATE TABLE Department ( Id INT PRIMARY KEY, Name CHAR(20) );INSERT INTO Department(Id, Name) VALUES(1,'IT');INSERT INTO Department(Id, Name) VALUES(2,'Sales');

第二题：Rank Scores

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking.

Note that after a tie, the next ranking number should be the next consecutive integer value.

In other words, there should be no "holes" between rank

select score, dense_rank()over(order by score desc)from Scores ;

附：建表及数据准备

DROP TABLE Scores PURGE;CREATE TABLE Scores(    Id INT PRIMARY KEY,    Score FLOAT);INSERT INTO Scores(Id, Score) VALUES(1,3.59);INSERT INTO Scores(Id, Score) VALUES(2,3.65);INSERT INTO Scores(Id, Score) VALUES(3,4.00);INSERT INTO Scores(Id, Score) VALUES(4,3.85);INSERT INTO Scores(Id, Score) VALUES(5,4.00);INSERT INTO Scores(Id, Score) VALUES(6,3.65);

0 0