LeetCode 刷题笔记，数据库专题，使用mysql 实现。

查询

197. Rising Temperature

主要使用了 built-in 的函数 subdate。DATE_SUB() 和 SUBDATE() 函数接受两个参数：

  • date 是 DATE 或 DATETIME 的起始值。
  • expr 是一个字符串，用于确定从起始日期减去的间隔值。type 是 expr 可解析的间隔单位，例如 DAY，HOUR 等
1
2
3

# Write your MySQL query statement below
# 一个表可以读两次
select w1.Id from Weather as w1, Weather as w2 where w1.Temperature > w2.Temperature and subdate(w1.RecordDate, 1) =w2.RecordDate

184. Department Highest Salary

数据库表的联结，首先查找每个部门的最高薪水（使用临时表的思想保存），然后使用和原来的表联结。该解法并没有使用到 group by

实现的时候， select.. join where condition order by ： 首先是选择，然后是不同表之间的连接，最后是以某种顺序进行排列。 mysql 中默认是按照升序排列，所以 select * from t1 order by col1 和 select * from t1 order by col1 asc 是等价的。

1
2
3

# Write your MySQL query statement below
select d.Name as Department, e.Name as Employee, e.Salary from Employee as e inner join Department as d on e.DepartmentId =d.Id where
(select Max(Salary) from Employee as e1 where e1.DepartmentId = e.DepartmentId) = e.Salary order by d.Name

183. Customers Who Never Order

1
2
3

# Write your MySQL query statement below
# 这个涉及到的是减法操作
select Name as Customers from Customers where Id not in (select CustomerId from Orders)

on 和where 的区别？ on 是先根据条件筛选，然后生成临时查询结果。where 是先连接生成临时查询结果，然后再筛选； on 是连接条件，代表两个表建立关系遵循的规则；where

185. Department Top Three Salaries

还是需要两个表格的连接，解这道题目的时候，可以使用这样的思路：如果一个组内的人的工资大于他的人的个数是小于3，那么这个人就是在最后的结果中。使用where 连接条件。

1
2
3
4
5
6
7

select D.Name as Department, E1.Name as Employee, E1.Salary as Salary
from Employee as E1, Department as D
where E1.DepartmentId =D.Id and 
(select count(distinct E2.Salary) 
from Employee E2 
where E1.DepartmentId = E2.DepartmentId and
E2.Salary > E1.Salary) <3

181. Employees Earning More Than Their Managers

对于 SELF JOIN 这类题目，直接看成两个表是比较理想的。

1
2

# Write your MySQL query statement below
select e1.Name as Employee from Employee e1, Employee e2 where e1.Salary > e2.Salary and e1.ManagerId =e2.Id

1
2
3

# Write your MySQL query statement below
# 这个涉及到的是减法操作
select Name as Customers from Customers where Id not in (select CustomerId from Orders)

182. Duplicate Emails

group by 搭配聚合函数使用，比如max min sum avg cout 五个函数；group by 还可以搭配 having 进行过滤。

1
2

# Write your MySQL query statement below
select Email from Person group by Email having count(Email) >1

180. Consecutive Numbers

1
2
3

# Write your MySQL query statement below
# 这个是连续的三个num，所以需要申请三个表；
select distinct l1.Num as ConsecutiveNums from Logs as l1, Logs as l2, Logs as l3 where l1.Id + 1 =l2.ID and l2.Id + 1 =l3.Id and l1.Num =l2.Num and l2.Num  =l3.Num

178. Rank Scores

这个解题思路是非常棒的：通过查找比其大的个数，然后判断其排名，是一种非常nice 的选择。

1

select S1.Score, (select count(distinct Score) from Scores as S2 where S2.Score >= S1.Score ) as Rank from Scores as S1 order by Score DESC

177. Nth Highest Salary

mysql 中 limit 和 offset 的用法：

1
2
3
4

语句1：select * from student limit 9,4
语句2：slect * from student limit 4 offset 9
// 语句1和2均返回表student的第10、11、12、13行  
//语句2中的4表示返回4行，9表示从表的第十行开始

写sql 语句都是作为 shell 语句的一部分。

1
2
3
4
5
6
7
8

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET n =N -1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary desc limit n, 1
  );
END

176. Second Highest Salary

所谓的第二大就是比 max 小的元素，这种思路是没有问题的

1
2

# Write your MySQL query statement below
select max(Salary) as SecondHighestSalary from Employee where Salary < (select max(Salary) from Employee)

175. Combine Two Tables

1
2

# Write your MySQL query statement below
select p.FirstName, p.LastName, a.City, a.State from Person as p left join Address as a on p.PersonId = a.PersonId

601. Human Traffic of Stadium

使用where 条件限制。从这个角度出发是没有那么难的，只是条件多了一点。

1
2
3
4
5
6
7
8
9

# Write your MySQL query statement below
SELECT DISTINCT s1.* FROM stadium s1, stadium s2, stadium s3
WHERE s1.people >= 100 AND s2.people >= 100 AND s3.people >= 100
AND (
    (s1.id+1=s2.id AND s1.id+2=s3.id AND s2.id+1=s3.id)   ## s1 s2 s3
    OR (s2.id+1=s1.id AND s2.id+2=s3.id AND s1.id+1=s3.id)    ## s2 s1 s3
    OR (s2.id+1=s3.id AND s2.id+2=s1.id AND s3.id+1=s1.id)    ## s2 s3 s1
) 
ORDER BY s1.id;

增加

删除

196. Delete Duplicate Emails

非常得直观，一个表可以重复读入两次。 一定要看成是两个表的操作。

1
2

# Write your MySQL query statement below
delete q from Person  p, Person  q where p.Email =q.Email and p.Id < q.Id

下面使用 select语句的是不行的。

1
2

# Write your MySQL query statement below
select min(Id) ,Email from Person group by Email

修改