用oracle分析函数实现的 Leetcode Database 题
第一题:Department Top Three Salaries
select a.depart, a.name, a.salary
from (
select d.name depart, e.name name, rank()over(partition by d.id order by e.salary desc) rank, e.salary salary
from department d, employee e
where d.id=e.departmentid
) a
where 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);