LEET CODE DATABASE 免费题目刷题记录

前言：我本地测试的数据库来自于Oracle的Scott.sql 的部分数据，由于和LC上的数据库内容比较相似只是字段名不太相同，所以下面除了答案的语句外，自己的分析是用的本地数据库的相关字段名，不要见怪~~~

175 Combine Two Tables

# Write your MySQL query statement below
# FirstName, LastName, City, State

select Person.FirstName,Person.LastName,Address.City,Address.State from Person LEFT JOIN Address ON Person.PersonId = Address.PersonId

oracle

这样写性能比较快

SELECT FirstName, LastName, City, State
FROM Address, Person
where Person.PersonId = Address.PersonId(+)

如果按照下面的样子写，性能会差一些。

/* Write your T-SQL query statement below */

select Person.FirstName,Person.LastName,Address.City,Address.State from Person LEFT JOIN Address ON Person.PersonId = Address.PersonId(+)

176 Second Highest Salary

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

第二高得，首先降序排列，然后偏移取第二个即可

mysql

SELECT (
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 
) as SecondHighestSalary

Oracle

似乎lc不支持 OFFSET X ROWS FETCH NEXT Y ROWS ONLY 的写法

这里用自己得数据库写写了

SELECT SAL FROM EMP ORDER BY SAL DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY

尽管这样不行，但是可以用rownum的伪列来操作，形如这样子

select SAL from (
        select SAL,rownum r1 from
            (select distinct SAL
             from EMP order by SAL desc)
        )
    where r1 = 2;

在这里，将SAL选出来之后，再外面套一层查询去查询，此时可以获取到oracle的隐藏变量 rownum 之后，根据rownum来选出对应的行即可。

177 Nth Highest Salary

mysql

MYSQL 的话

，注意一下函数咋写就行了，注意里面不能直接N-1。似乎只能重定义一个变量

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  DECLARE M INT;
  SET M = N-1;
  RETURN (
      # Write your MySQL query statement below.
    SELECT (
    SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET M 
    )  
  );
END

Oracle

封装一下之前的方法就可以了。

CREATE FUNCTION getNthHighestSalary(N IN NUMBER)
  RETURN NUMBER IS
  result NUMBER;
  BEGIN
    /* Write your PL/SQL query statement below */
    SELECT SAL INTO result FROM EMP ORDER BY SAL DESC OFFSET N-1 ROWS FETCH NEXT 1 ROWS ONLY;
    RETURN result;
  END;
SELECT getNthHighestSalary(1) FROM dual;

之前的那个玩法也很简单，把1改为N就可以了

CREATE FUNCTION getNthHighestSalary(N IN NUMBER) RETURN NUMBER IS
result NUMBER;
BEGIN
    /* Write your PL/SQL query statement below */
select salary into result from (
        select salary,rownum r1 from
            (select distinct salary from Employee order by salary desc)
        )
    where r1 = n;
    RETURN (result);
END;

178 Rank Score

Oracle

测试数据很坑，好像有1.0000000001的数据，按照网上的说法还有点问题。用trunc函数对输出数据做规定。但是加了trunc后，速度就变慢了...

这是网上一种比较巧妙的方法是使用一个表的score对另一个表的各个score进行比较，实际上是通过count score与 s.score的比较，算算>=的值的数量，来进行排序。

SELECT
  trunc(Score,2) SCORE,
  (SELECT count(SCORE) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank
FROM Scores
ORDER BY Score desc

180 Consecutive Numbers

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

找出连续出现三次的数字，后面还有个题目是这道题目的加强版本

SELECT DISTINCT l1.Num  ConsecutiveNums
FROM Logs l1,
     Logs l2,
     Logs l3
WHERE l1.Id = l2.Id - 1
  AND l2.Id = l3.Id - 1
  AND l1.Num = l2.Num
  AND l2.Num = l3.Num;

181 Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

oracle

大概的思路是这样子，把数据库拆成两部分，一部分查ID，一部分查managerID

/* Write your PL/SQL query statement below */
SELECT E1.Name Employee 
FROM 
    Employee  E1,
    Employee  E2
WHERE
    E1.ManagerID = E2.ID
        AND E1.Salary > E2.Salary;

196 Duplicate Emails

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

Oracle

要找到非重复Email 一种方法是用 group by x having 的方法。这也是我第一个想到的方法

SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1

看论坛里面的方法不是很好用

197 Customers Who Never Order

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

Oracle

用 left join

/* Write your PL/SQL query statement below */
SELECT C.NAME FROM Customers C
    LEFT JOIN (
        SELECT DISTINCT CUSTOMERID FROM ORDERS
    )  O
    ON C.ID = O.CUSTOMERID
WHERE O.CUSTOMERID IS NULL

用 in

/* Write your PL/SQL query statement below */
SELECT C.NAME FROM Customers C
    WHERE C.ID NOT IN 
        (
            SELECT CustomerId FROM Orders O
        )

等等...

为啥它的顺序是反过来的？

184. Department Highest Salary

The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.

选出每个部门中

mysql

# Write your MySQL query statement below
SELECT
  Department.NAME AS Department,Employee.NAME AS Employee,employee.salary AS Salary
FROM
     employee JOIN Department
         ON Department.id  = employee.DepartmentId 
WHERE
    (employee.DepartmentId ,employee.salary) IN
    (
        SELECT employee.DepartmentId  , MAX(employee.salary)
        FROM employee
        GROUP BY employee.DepartmentId 
    )

Oracle

/* Write your PL/SQL query statement below */
SELECT
  Department.NAME Department,employee.NAME employee,employee.salary salary
FROM
     employee JOIN Department
         ON Department.id  = employee.DepartmentId 
WHERE
    (employee.DepartmentId ,employee.salary) IN
    (
        SELECT employee.DepartmentId  , MAX(employee.salary)
        FROM employee
        GROUP BY employee.DepartmentId 
    )
    ORDER BY salary

注意这里join的用法。LEFT JOIN , RIGHT JOIN ,JOIN是不一样的，很坑的是样例就考察了你这个东西

。题目要求选出每个部门中，赚钱最多的人名，和薪水

第一步，自然是先建立一个虚表，其中应当包含：人名，薪水和部门名称

-- SELECT EMP.ENAME,EMP.SAL ,D.DNAME FROM
              EMP INNER JOIN DEPT D on EMP.DEPTNO = D.DEPTNO

之后，从这个虚标TV-1中设置选择条件。要求选出当前部门薪水最多的人的人名。由于有三个组别，所以要用到GROUP BY 如果就只是 EMP 一张表的话，是非常好说的。如下的语句即可

SELECT EMP.DEPTNO, MAX(EMP.SAL)
FROM EMP
GROUP BY EMP.DEPTNO
30  2850
10  5000
20  3000

但是，为了加上人名，就要多费一番周折，对此，需要将 EMP.DEPTNO 和MAX(SAL)选出来，用In子句限定，最终得到结果

SELECT EMP.ENAME,EMP.SAL ,D.DNAME
  FROM
       EMP JOIN DEPT D on EMP.DEPTNO = D.DEPTNO
  WHERE (
            (EMP.DEPTNO,EMP.SAL) IN
                  (
SELECT EMP.DEPTNO, MAX(EMP.SAL)
FROM EMP
GROUP BY EMP.DEPTNO
                      )
            )
KING    5000.00 ACCOUNTING
FORD    3000.00 RESEARCH
BLAKE   2850.00 SALES

185. Department Top Three Salaries

The Employee table holds all employees. Every employee has an Id, and there is also a column for the department Id.

The Department table holds all departments of the company.

Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.

目标十分明确，找出每个部门排名前三的工资。

这里要用到 OVER ( PARITITION BY X ORDER BY Y DESC) 的玩法。在此不展开，可以看看别人的博客

有一个坑点是排名前三，意味着如果第二的工资有两个人相同，那么结果出来可能会大于三行

Oracle对应的函数是 dense_rank() ，也就是密集排序，不会把相同的排名给跳过去

dense_rank与rank()用法相当，但是有一个区别：dence_rank在并列关系是，相关等级不会跳过。rank则跳过.

oracle

SELECT Department.Name Department,RANKED_TABLE.ENAME Employee ,RANKED_TABLE.SAL Salary 
FROM (SELECT
             EMP1.Name ENAME,
             EMP1.DepartmentId  DEPTID,
             EMP1.Salary  SAL,
             dense_rank() OVER (PARTITION BY EMP1.DepartmentId ORDER BY EMP1.Salary DESC) DEPTNO_COUNT
      FROM Employee  EMP1) RANKED_TABLE INNER JOIN Department ON RANKED_TABLE.DEPTID = Department.Id
WHERE RANKED_TABLE.DEPTNO_COUNT <= 3

196. Delete Duplicate Emails

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

这个很简单，只要查两次就好了，基本操作

mysql

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

197. Rising Temperature

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

找出气温上升的ID，利用 JOIN 即可完成操作

Oracle中没有DATEDIFF，直接减就可以了这也是它强大之处！

oracle

SELECT
    weather.id Id
FROM
    weather
        JOIN
    weather w ON weather.RecordDate - w.RecordDate = 1
        AND weather.Temperature > w.Temperature

262. Trips and Users

感觉这题目出的不好，表述不是很明确

'Trips 表保存所有出租车行程。每个行程都有一个唯一的Id，而Client_Id和Driver_Id都是“Users”表中Users_Id的外键。状态是ENUM类型（'completed'，'cancelled_by_driver'，'cancelled_by_client'）。 Users`表包含所有用户。每个用户都有一个唯一的Users_Id，而Role是一个ENUM类型（'client'，'driver'，'partner'）。编写一个 SQL 查询，以查找在2013年10月1-3日，

对于上面的表，您的SQL查询应返回以下行，取消率将四舍五入为 两个 小数位。

这道题给了我们一个Trips表里面有一些Id和状态，还有请求时间，然后还有一个Users表，里面有顾客和司机的信息，然后有该顾客和司机有没有被Ban的信息，让我们返回一个结果看某个时间段内由没有被ban的顾客提出的取消率是多少，其实题目没有说清楚顾客到底包不包括司机，其实是包括的，由司机提出的取消请求也应计算进去【这个 要自己试...】，我们用Case When ... Then ... Else ... End关键字来做，我们用cancelled%来表示开头是cancelled的所有项，这样就包括了driver和client，然后分母是所有项，限制条件里限定了时间段，然后是没有被ban的，由于结果需要保留两位小数，所以我们用Round关键字且给定参数2即可

SELECT 
    request_at AS Day, 
    round(
        sum(
            CASE WHEN   -- 这里临时生成了一个字段，由status字段决定 若是完成字段值为0 反之为1  也就是是否被取消
                status="completed" THEN 0 
                ELSE 1 
            END )       -- 对这个字段使用sum函数即可求得被取消了的个数
            /count(*),  2)      -- 除以总个数后四舍五入2位即为被取消的概率
        AS "Cancellation Rate"
FROM  
    Trips t INNER JOIN Users u ON t.Client_Id = u.Users_Id and u.Banned='No'    -- 限定为非ban用户
WHERE request_at  BETWEEN "2013-10-01" AND "2013-10-03"    -- 限定日期
GROUP BY request_at -- 对日期分组

601. Human Traffic of Stadium

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id , date , people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

找出连续三天人数大于 100 的行

mysql

推荐一种比较蠢的方法，一步步做呗。实际上也是官方给出的解答。评论区有用变量实现的，有点看不懂。就不记录了。这道题和上面哪道题不一样的地方在于，一个是找相同的数字，一个是找大于100的人数对应的ID。大于的就要复杂一些了

考虑s1，s2和s3是相同的，我们可以考虑其中一个来考虑我们应该添加哪些条件来过滤数据并获得最终结果。以s1为例，它可能存在于连续3天的开始，或中间或最后一天。

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 - s2.id = 1
        AND s1.id - s3.id = 2
        AND s2.id - s3.id = 1
    )
    OR (
        s2.id - s1.id = 1
        AND s2.id - s3.id = 2
        AND s1.id - s3.id = 1
    )
    OR (
        s3.id - s2.id = 1
        AND s2.id - s1.id = 1
        AND s3.id - s1.id = 2
    )
)
ORDER BY
    s1.id

620. Not Boring Movies

付费账号，想要拥有，一个月租金太贵了。我只是来学数据库的哭唧唧

X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions.

Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.

• 奇数编号 ID MOD 2 = 1
• 没有boring NOT LIKE '%boring%'
• rating降序 ORDER BY RAGIN DESC

SELECT * FROM cinema WHERE description NOT LIKE '%boring%' AND ID MOD 2 = 1 ORDER BY RATING DESC

626. Exchange Seats

Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

The column id is continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result fo r Mary?

调换相邻两个数据的位置。

+---------+---------+ ===>>> 
|    id   | student | ===>>>
+---------+---------+ ===>>>
|    1    | Abbot   | ===>>>
|    2    | Doris   | ===>>>
|    3    | Emerson | ===>>>
|    4    | Green   | ===>>>
|    5    | Jeames  | ===>>>
+---------+---------+ ===>>>
+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  |
+---------+---------+

我想到的是用when子句。

• 如果id为偶数，那么id -1
• 如果id为奇数 并且他说最大的 那么 id 不变
• 如果id是奇数 那么id+1 可以得到这样的表格

2   Abbot
1   Doris
4   Emerson
3   Green
5   Jeames

然后重新进行 排序 即可

mysql

SELECT CASE
         WHEN ID MOD 2 != 0 AND ID = (SELECT MAX(ID) FROM seat S2) THEN id
         WHEN ID MOD 2 != 0 THEN ID + 1
         ELSE ID - 1
           END AS ID , student
FROM seat S1 ORDER BY ID

答案是更加狠的操作

Bit manipulation expression (id+1)^1-1 can calculate the new id after switch.

COALESCE 函数：

将相同的值作为 expression 返回。

返回 表达式 中第一个非空表达式，如有以下语句：

SELECT COALESCE(NULL,NULL,3,4,5) FROM dual

其返回结果为：3

SELECT
    s1.id, COALESCE(s2.student, s1.student) AS student
FROM
    seat s1
        LEFT JOIN
    seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id
ORDER BY s1.id;

后记：做完了LEETCODE上数据库的题目，感觉自己对于数据库的理解还是太浅薄了。以后搞到了付费账号之后，再来把锁住的题目做了吧