Leetcode Database - 我的汇总

最新推荐文章于 2023-04-15 11:26:12 发布

Sugeei

最新推荐文章于 2023-04-15 11:26:12 发布

阅读量1.2k

点赞数

分类专栏： SQL SQL oracle

本文链接：https://blog.csdn.net/huxian370/article/details/48051725

版权

SQL oracle 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

SQL

2 篇文章 0 订阅

订阅专栏

https://leetcode.com/

第一次刷了3个sql 的题。

2015.8.14

在本地测试用的Linux + oracle， leetcode 上用的是mySQL环境。

三个题都在本地测试通过了，到mySQL 上面就各种编译不通过，折腾了好几次才被accept

不过也算是对oracle 与 mySQL 的区别有了一些了解。

第一题 Weather

第二题 Customer with no order

第三题 Rank Scores

第四题 DepartmentTop Three Salaries

第五题 ConsecutiveNumbers

第六题Department HighestSalary

第七题Second highest salary

第八题Nth Highest Salary

第九题Delete DuplicateEmails

第十题Duplicate Emails

第十一题Trips and Users

第十二题Delete Duplicate Emails

第一题

Given a Weather table, write a SQL query tofind all dates' Ids with higher temperature compared to its previous(yesterday's) dates.

建表及插入测试数据

DROP TABLE weather PURGE;

CREATE TABLE weather(id INT PRIMARY KEY, recorddate DATE, temperature INT );

INSERT INTO weather(id, recorddate, temperature) VALUES(1, SYSDATE,10);

INSERT INTO weather(id, recorddate, temperature) VALUES(2, SYSDATE+1,25);

INSERT INTO weather(id, recorddate, temperature) VALUES(3, SYSDATE+2,20);

INSERT INTO weather(id, recorddate, temperature) VALUES(4, SYSDATE+3,30);

oracle 版本的查询

SELECT a.id

FROM weather a, weather b

WHERE to_date(a.recorddate-1 ,'YYYY-MM-DD')=to_date(b.recorddate ,'YYYY-MM-DD')

AND a.temperature>b.temperature;

mySQL 版本的查询

SELECT a.Id

FROM Weather a, Weather b
WHERE TO_DAYS(a.Date)-1=TO_DAYS(b.Date) AND a.Temperature>b.Temperature;

比较

Oracle	mySQL
to_date(date)	TO_DAYS(date)
to_date( '2008-09-08')

第二题

Suppose that a website contains two tables,the Customers table and the Orders table. Write a SQL query to find allcustomers who never order anything.

建表及插入测试数据

DROP TABLE Customers PURGE;

CREATE TABLE Customers ( Id INT PRIMARY KEY, Name CHAR(20) );

INSERT INTO Customers (Id, Name) VALUES(1,'joe');

INSERT INTO Customers (Id, Name) VALUES(2,'henry');

INSERT INTO Customers (Id, Name) VALUES(3,'sam');

INSERT INTO Customers (Id, Name) VALUES(4,'max');

DROP TABLE Orders PURGE;
CREATE TABLE Orders ( Id INT PRIMARY KEY, CustomerId INT ) ;

INSERT INTO Orders (Id, CustomerId) VALUES(1,3);

INSERT INTO Orders (Id, CustomerId) VALUES(2,1);

oracle 版本的查询

SELECT Name From Customers

WHERE ( SELECT COUNT(1) FROM Orders WHERE Orders.CustomerId=Customers.Id) = 0;

第三题

Rank Scores

Write a SQL query to rank scores. If there is a tie between twoscores, both should have the same ranking. Note that after a tie, the nextranking number should be the next consecutive integer value. In other words,there should be no "holes" between rank.

建表及插入测试数据

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);

oracle 版本的查询

SELECT a.Score, b.rank

FROM Scores a, (

SELECT sst.Id, COUNT(temp.ts) +1 rank

FROM Scores sst, (

SELECT DISTINCT Score ts FROM Scores) temp

WHERE sst.Score<temp.ts(+)

GROUP BY sst.Id ) b

WHERE a.Id=b.Id

ORDER BY a.Score DESC;

mySQL 版本的查询

SELECT a.Score, b.rank
FROM Scores a, (
    SELECT sst.Id,COUNT(temp.ts)+1 rank
    FROM Scores sst LEFT JOIN (SELECT DISTINCT Score ts FROM Scores) temp
    ON sst.Score < temp.ts
    GROUP BY sst.Id) b
WHERE a.Id=b.Id
ORDER BY a.Score DESC;

比较

Oracle left join

mySQL left jion

table_a.column = table_b.column(+)

table_a LEFT JOIN table_b

ON CONDITION

Oracle 分析函数实现

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

2015.8.17

第四题

Department Top Three Salaries

建表及插入测试数据

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');

假设待排名次的分数是： 99， 98，98，97

名次的两种排法：

有间隔的方法

无间隔的方法

第一名 99

第二名 98

第四名 97

第一名 99

第二名 98

第三名 97

oracle 版本的查询-有间隔 (worksfor mySQL)

SELECT d.Name Department, temp.Name Employee, temp.Salary Salary

FROM Department d,

( SELECT em.Id Id,em.Name Name,em.Salary Salary,em.DepartmentId dep

FROM Employee em

WHERE (

#count records whose salary large than outer salary

SELECT count(1) FROM Employee emp

WHERE em.DepartmentId=emp.DepartmentId

And em.Salary<emp.Salary

)<=2) temp

#for record with highest salary, 0 record large than it; for record with second highest #salary, 1 record large than it. To get top three, records large than target record should #<=2

WHERE temp.dep=d.Id

ORDER BY Department ,Salary DESC;

#in this way, as ‘count’ does not remove duplicate records, there will be a gap after #records with same salary

oracle 版本的查询-无间隔-1 (worksfor mySQL)

SELECT d.Name Department, temp.Name Employee, temp.Salary Salary

FROM Department d,

( SELECT em.Id Id,em.Name Name,em.Salary Salary,em.DepartmentId dep

FROM Employee em

WHERE (

#with ‘count distinct’, the result is same as with no duplicate records

SELECT count(distinct emp.Salary) FROM Employee emp

WHERE em.DepartmentId=emp.DepartmentId

And em.Salary<=emp.Salary

)<=3) temp

WHERE temp.dep=d.Id

ORDER BY Department ,Salary DESC;

Oracle 分析函数实现

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;

第五题

Consecutive Numbers

Write a SQL query to find all numbers that appear at least three timesconsecutively.

+----+-----+

| Id | Num |

+----+-----+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

+----+-----+

For example, given the above Logs table, 1 is the only number that appearsconsecutively for at least three times.

建表及插入测试数据

create table Logs(id int,num int);

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);

oracle 版本的查询-1

Select distinct la.num
From Logs la ,Logs lb,Logs lc
where la.id+1=lb.id and lb.id+1=lc.id and la.num=lb.num and lb.num=lc.num;

needto fulfill two conditions:

1. 3 rows selected in inner query

2. all three records have same 'num' value

below code fulfill condition 2, but not 1

oracle版本的查询-2

Select distinct la.num

From Logs la

Where(

#if all the three records have same num value, the ‘count’get result 1

Select count(distinct lb.num)

From Logs lb

Where la.id<=lb.id and lb.id<=la.id+2

#filter records with id between la.id and la.id+2, get no more than 3 records

)=1;

mySQL 版本的查询 (error in leetcode)

Select distinct la.num

From Logs la

Where(

#if all the three records have same num value, the ‘count’get result 1

Select count(distinct lb.num)

From Logs lb

Where la.id<=lb.id and lb.id<=la.id+2

#filter records with id between la.id and la.id+2, get no more than 3 records

)=1

and

# make sure 3 rows selected

( Select count(1)

From Logs lb

Where la.id<=lb.id and lb.id<=la.id+2 )=3;

第六题

Department Highest Salary

TheEmployee table holds all employees. Every employee has an Id, a salary, andthere is also a column for the department Id.

| Id | Name  | Salary | DepartmentId |

| 1  | Joe   | 70000  | 1            |

| 2  | Henry | 80000  | 2            |

| 3  | Sam   | 60000  | 2            |

| 4  | Max   | 90000  | 1            |

The Department tableholds all departments of the company.

+----+----------+

| Id | Name     |

+----+----------+

| 1  | IT       |

| 2  | Sales    |

+----+----------+

Write a SQL query to find employees who have thehighest salary in each of the departments. For the above tables, Max has thehighest salary in the IT department and Henry has the highest salary in theSales department.

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT         | Max      | 90000  |

| Sales      | Henry    | 80000  |

+------------+----------+--------+

建表及插入测试数据

oracle 版本的查询 (works for mySQL)

SELECT dep.name depart, ttm.na employee, ttm.sal salary

FROM Department dep,(

SELECT emp.Name na, emp.salary sal, emp.departmentid dd

FROM Employee emp,(

SELECT departmentid did, MAX(salary) ms

FROM Employee

GROUP BY departmentid) temp

WHERE emp.salary=temp.ms

AND temp.did=emp.departmentid) ttm

WHERE dep.id=ttm.dd;

mysql查询2 – 不正确的 employee name

分组查询时，select中不能出现group by及聚合函数中都没有用到的列名。

select dep.name department, max(em.salary) salary

from Employee em, Department dep

where em.departmentid=dep.id

group by dep.name ;

mysql查询3

select eb.dname department, ea.name employee, ea.salary salary

from employee ea, (

select max(salary) ms, dep.name dname

from Employee em, Department dep

where em.departmentid=dep.id

group by em.departmentid ) eb

where ea.salary=eb.ms;

第七题

Second highest salary

Write aSQL query to get the second highest salary from the Employee table.

+----+--------+

| Id | Salary |

+----+--------+

| 1  | 100    |

| 2  | 200    |

| 3  | 300    |

+----+--------+

Forexample, given the above Employee table, the second highest salary is 200. Ifthere is no second highest salary, then the query should return null.

建表及插入测试数据

DROP TABLE Employee PUGRE;

CREATE TABLE Employee(id INT, salary INT);

INSERT INTO Employee(id,salary) VALUES(1,100);

oracle 版本的查询 (works for mySQL)

Functions like MAX, MIN, SUM will return null if the query result is“norow selected”.

SELECT MAX(em.salary)

FROM Employee em

WHERE em.salary< ( SELECT MAX(salary) FROM Employee );

第八题

Nth Highest Salary

建表及插入测试数据

知识点：

if N is out of order, return null

MySQL IFNULL()函数用法

IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

mysql delimiter

告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号。

在定义Function 时，将delimiter 改为 $$ 或者其它可唯一作为界定的符号。否则函数定义未录入完成前，mysql遇到分号就判定函数结束并开始执行，这样会导致错误。

delete function

DROP FUNCTION getNthHighestSalary;

call function

SELECT getNthHighestSalary(8);

mysql LIMIT

select * from table limit m,n

其中m是记录开始的index，0表示第一条记录，从第m+1条开始取。n是指取n条记录。select * from tablename limit 2,4即取出第3条至第6条，4条记录。

mySQL中实现rownum.的功能

select @rownum := @rownum+1 as rn, temp.sal ss

from (

select distinct e.salary sal

from Employee e

order by e.salary DESC

) temp , (select @rownum :=0) r ；

输出为（‘rn’ is the rownum ）

mySQL 版本的查询

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

select ifnull(ttm.salary,null) salary

from (

select @row_num := @row_num+1 rn , temp.sal salary

from (

select distinct em.salary sal

from Employee em order by em.salary DESC

) temp, (SELECT @row_num := 0) r

) ttm

where ttm.rn=N

);

END

# ttm.rn=N means get the top one

Result

第九题

Delete Duplicate Emails

Write a SQL query to delete all duplicate email entries in a table namedPerson, keeping only unique emails based on its smallest Id.

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | john@example.com |

| 2 | bob@example.com |

| 3 | john@example.com |

+----+------------------+

Id is the primary key column for this table.

For example, after running your query, the above Person table should havethe following rows:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | john@example.com |

| 2 | bob@example.com |

+----+------------------+

建表及插入测试数据

drop table person purge;

create table person(id int,email char(50));

insert into person(id,email) values(1,'jo@ex.com');

insert into person(id,email) values(2,'bob@ex.com');

insert into person(id,email) values(3,'jo@ex.com');

oracle 版本的查询 (works for mysql)

select a.id,a.email

from Person a, Person b

where ( a.email=b.email and a.id<b.id )

union

select c.id,c.email

from Person c

where ( select count(1)

from Person d

where c.email=d.email )=1;

oracle 版本的查询 (works for mysql) – not pass yet

select min(a.id) id, a.email from Person a group by a.email ;

2015.8.19

https://leetcode.com

第十题

Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

For example, your query should return the following for the above table:

+---------+

| Email |

+---------+

| a@b.com |

+---------+

建表及插入测试数据

与自身做连接查询，找出那些email相等但id不相等的行。

oracle 版本的查询 (works for mysql)

select distinct a.email

from Person a, Person b

where ( a.email=b.email and a.id<b.id )

第十一题

TheTrips table holds all taxi trips. Each trip has a unique Id, while Client_Idand Driver_Id are both foreign keys to the Users_Id at the Users table. Statusis an ENUM type of (‘completed’, ‘cancelled_by_driver’,‘cancelled_by_client’). -

+----+-----------+-----------+---------+--------------------+----------+

+----+-----------+-----------+---------+--------------------+----------+

|1 | 1 | 10 | 1 | completed |2013-10-01|

|2 | 2 | 11 | 1 | cancelled_by_driver|2013-10-01|

|3 | 3 | 12 | 6 | completed |2013-10-01|

|4 | 4 | 13 | 6 | cancelled_by_client|2013-10-01|

|5 | 1 | 10 | 1 | completed |2013-10-02|

|6 | 2 | 11 | 6 | completed |2013-10-02|

|7 | 3 | 12 | 6 | completed |2013-10-02|

|8 | 2 | 12 | 12 | completed |2013-10-03|

|9 | 3 | 10 | 12 | completed |2013-10-03|

|10 | 4 | 13 | 12 | cancelled_by_driver|2013-10-03|

+----+-----------+-----------+---------+--------------------+----------+

TheUsers table holds all users. Each user has an unique Users_Id, and Role is anENUM type of (‘client’, ‘driver’, ‘partner’).

+----------+--------+--------+

|Users_Id | Banned | Role |

+----------+--------+--------+

| 1 | No | client |

| 2 | Yes | client |

| 3 | No | client |

| 4 | No | client |

| 10 | No | driver |

| 11 | No | driver |

| 12 | No | driver |

| 13 | No | driver |

+----------+--------+--------+

Writea SQL query to find the cancellation rate of requests made by unbanned clientsbetween Oct 1, 2013 and Oct 3, 2013. For the above tables, your SQL queryshould return the following rows with the cancellation rate being rounded totwo decimal places.

+------------+-------------------+

| Day | Cancellation Rate |

+------------+-------------------+

|2013-10-01 | 0.33 |

|2013-10-02 | 0.00 |

|2013-10-03 | 0.50 |

+------------+-------------------+

建表及插入测试数据 (for oracle)

DROP TABLE Trips PUGRE;

CREATE TABLE Trips( Id INT, Client_Id INT, Driver_Id INT, City_Id INT, Status CHAR(20), Request_at CHAR(20));

INSERT INTO Trips(Id, Client_Id, Driver_Id, City_Id, Status, Request_at) VALUES

( 1,1,10,1,'completed','2013-10-01' ),

( 3,3,12,6,'completed','2013-10-01' ),

( 4,4,13,6,'cancelled_by_client','2013-10-01' ),

( 5,1,10,1,'completed','2013-10-02' ),

( 6,2,11,6,'completed','2013-10-02' ),

( 7,3,12,6,'completed','2013-10-02' ),

( 8,2,12,12,'completed','2013-10-03' ),

( 9,3,10,12,'completed','2013-10-03' ),

( 10,4,13,12,'cancelled_by_driver','2013-10-03' );

DROP TABLE Users PUGRE;

CREATE TABLE Users( Users_Id INT, Banned CHAR(10), Role char(10));

INSERT INTO Users(Users_Id, Banned, Role) VALUES( 1,'No','client'),( 2,'Yes','client'),( 3,'No','client'),( 4,'No','client'),( 10,'No','driver'),( 11,'No','driver'),( 12,'No','driver'),( 13,'No','driver');

Oracle 初级版

select ta.Day Day, round(sum(ta.flag)/count(*),2) "Cancellation Rate"

from ( select t1.status sta, t1.Request_at Day , case t1.status when 'completed' then 0 else 1 end as flag

from Trips t1, Users u1

where t1.Client_Id = u1.Users_Id and u1.Banned = 'No'

and t1.Request_at between '2013-10-01' and '2013-10-03') ta

group by ta.Day

order by ta.Day;

Oracle 版本的查询 (works for mysql)

select t1.Request_at Day, round( sum( case t1.status when 'completed' then 0 else 1 end)/count(*),2) "Cancellation Rate"
from Trips t1, Users u1
where t1.Client_Id = u1.Users_Id and u1.Banned = 'No'
and t1.Request_at between '2013-10-01' and '2013-10-03'
group by t1.Request_at
order by t1.Request_at;

比较

Oracle	mySQL
支持PURGE	不支持 PURGE
No ENUM type	CREATE TABLE Y( Id INT, Client_Id INT, Driver_Id INT, City_Id INT, Status ENUM( 'completed', 'cancelled_by_driver', 'cancelled_by_client' ), Request_at DATE);