LeetCode数据库题目总结

596.超过5名学生的课

pic1

方法1.使用GROUP BY和子查询

想法

我们可以先获得每个课程的学生数，再选择超过五个学生的课程。

算法

为了获得每个课程的学生数，可以使用函数GROUP BY和COUNT:

SELECT class, COUNT(DISTINCT student)
  FROM courses
GROUP BY class
;
pic2

接着利用子查询挑选出符合条件的课程：

SELECT class
  FROM 
      (SELECT class, COUNT(DISTINCT student) AS num
         FROM courses
       GROUP BY class) AS temp_table
 WHERE num >= 5
;

方法2.使用GROUP BY和HAVING函数

算法

子查询是对GROUP BY进行条件限制的一个方法，另一种方法是用HAVING。

MYSQL

SELECT class
  FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5

183.从不订购的客户

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。


pic3

解答

方法.子查询和NOT IN结合

算法

如果我们已知了一列有订单的客户，那么没有订单的客户就很清楚了。
利用下面的语句可以选出有订单的客户：

SELECT customerid FROM Orders;

接着，用NOT IN语句来选出不在上面列表中的客户。

MYSQL

SELECT Customers.Name AS 'Customers'
  FROM Customers
 WHERE Customers.id NOT IN
（
 SELECT CustomerId FROM orders
）;

182.查找重复的电子邮箱

编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。


pic4

方法1.GROUP BY和临时表

算法

重复的电子邮箱出现大于等于两次，先计算每个邮箱出现的次数：

SELECT Email, count(Email) AS num
  FROM Person
GROUP BY Email;

把上面的表作为临时表，最后的语句：

SELECT Email FROM 
(
  SELECT Email, count(Email) AS num
    FROM Person
  GROUP BY Email
)AS statistic
WHERE num > 1
;

方法2.GROUP BY和HAVING

对GROUP BY加条件的常用方法是HAVING，更简单且有效率

MYSQL

SELECT Email
  FROM Person
GROUP BY Email
HAVING count(Email) > 1;

627.交换工资

给定一个 salary表，如下所示，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。


pic5

方法：UPDATE和CASE...WHEN

MYSQL

UPDATE salary
SET 
    sex = CASE sex
        WHEN 'm' THEN 'f'
        ELSE 'm'
    END;

196.删除重复的电子邮箱

pic6

方法：DELETE和WHERE

算法

用Email来join这个表和它自身：

SELECT p1.*
  FROM Person p1, Person p2
 WHERE p1.Email = p2.Email;

然后我们查找出拥有相同Email的较大ID,用WHERE来实现：

SELECT p1.*
  FROM Person p1, Person p2
 WHERE p1.Email = p2.Email
   AND p1.Id > p2.Id;

现在已经得到了所有需要删除的记录，接下来只需要用DELETE函数。

MYSQL

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

197.上升的温度

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。


pic7

方法1：使用JOIN和DATEDIFF()

算法

MYSQL的DATEDIFF函数可以用来比较两个日期值，我们可以join这个表自身并使用DATEDIFF函数。

MYSQL

SELECT weather.id AS 'Id'
  FROM weather
       JOIN 
       weather w ON DATEDIFF(weather.date, w.date) = 1
        AND weather.Temperature > w.Temperature;

方法2：使用JOIN和TO_DAYS

算法

对日期做差，也可以先对日期用TO_DAYS函数，再求差

MYSQL

SELECT a.Id
  FROM 
  Weather a 
 INNER JOIN Weather b
 ON TO_DAYS(a.RecordDate) = TO_DAYS(b.RecordDate) + 1
  AND a.Temperature > b.Temperature

184.部门工资最高的员工

pic8

方法：使用JOIN和IN

算法

Employee表里包含工资和部门id，我们可以得到各部门里最高的工资。

SELECT DepartmentId, MAX(Salary)
  FROM Employee
GROUP BY DepartmentId

可能存在不止一个员工有同样的最高的工资，所以不应该加上员工姓名。

接着，把employee表和department表join起来，找出来(DepartmentId, Salary)与上面临时表相等的行

SELECT Department.Name AS 'Department', Employee.Name AS 'Employee', Salary
  FROM Employee
  JOIN Department ON Employee.DepartmentId = Department.Id
 WHERE (Employee.DepartmentId, Salary) IN
       (SELECT DepartmentId, MAX(Salary)
          FROM Employee
        GROUP BY DepartmentId);

626.换座位

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？


pic9.png

注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

方法：CASE

算法

对于位置id是奇数的学生，除了最后一个奇数位置之外的学生新id是(id+1)。而位置id是偶数的学生，新id是(id-1)。我们可以用下面这个语句来获取全部的位置数：

SELECT COUNT(*) AS counts
  FROM seat

然后，用CASE语句和MOD()函数来改变每个学生的位置。

MYSQL

SELECT (CASE 
           WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
           WHEN MOD(id, 2) != 0 AND counts = id THEN id
           ELSE id - 1
        END) AS id,
        student
  FROM
        seat,
        (SELECT COUNT(*) AS counts
           FROM seat) AS seat_counts
ORDER BY id ASC;

180.连续出现的数字

pic10.png

方法：DISTINCT和WHERE

算法

连续出现意味着相同Num的Id是相邻的。需要查找出至少连续出现三次的数字，可以用三个Logs表，测试三个连续的id出现的数字是否相同。

SELECT *
  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
;

上面的语句取出的表形式应该是：


pic11.png

然后选取上表中任意的Num列即可，注意需要加上DISTINCT关键字。

MYSQL

 SELECT DISTINCT
    l1.Num AS 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
;

177.第N高的薪水

编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+

方法：LIMIT，OFFSET

MYSQL

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
  SET N = N-1;
  RETURN (
      SELECT DISTINCT Salary
        FROM Employee
      ORDER BY Salary DESC
      LIMIT 1 OFFSET N     
  );
END

185.部门工资前三高的员工

Employee 表包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id 。

+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
+----+-------+--------+--------------+

Department 表包含公司所有部门的信息。

+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+

方法1：JOIN和子查询

算法

top3的工资意味着没有3个salary比它更高了：

SELECT e1.Name AS 'employee', e1.Salary
  FROM Employee e1
 WHERE 3 >
(
     SELECT count(distinct e2.salary)
       FROM Employee e2
      WHERE e2.Salary > e1.Salary
)
;

上述语句返回的表如下：


pic12.png

然后需要join另一张表：

SELECT 
     d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM 
    Employee e1
       JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT 
           COUNT(DISTINCT e2.Salary)
          FROM
         Employee e2
          WHERE
           e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;

方法二：DENSE_RANK

MS SQL server

SELECT department, employee, salary 
FROM (
    
  SELECT b.name AS department, a.name AS employee, salary,
    dense_rank() over(partition by departmentid order by salary desc) AS rn
FROM employee a 
LEFT JOIN department b
ON a.departmentid=b.id 
    WHERE b.name is not null) c
WHERE rn <= 3 
ORDER BY department,salary DESC

601.体育馆的人流量

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 stadium：

+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
对于上面的示例数据，输出为：

+------+------------+-----------+
| id | date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
Note:
每天只有一行记录，日期随着 id 的增加而增加。

方法：JOIN和WHERE

想法

先选出所有人数大于100的日期，再用这个临时的表JOIN它自身，最后用WHERE来找连续的日期

算法

第一步：选出所有人数大于100的日期，再用这个临时表JOIN它自身

SELECT DISTINCT t1.*
  FROM stadium t1, stadium t2, stadium t3
 WHERE t1.people >= 100 AND t2.people >= 100 AND t3.people >= 100
;

总共有六天的人数大于100，因此三个表JOIN的结果是6 * 6 * 6 = 216行。
以t1为例，它可以是连续三天的第一天、第二天或者第三天，即：

t1是第一天：(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id = 1)
-- t1, t2, t3
t1是第二天：(t2.id - t1.id = 1 and t2.id - t.id = 2 and t1.id - t3.id = 1)
-- t2, t1, t3
t1是第三天：(t3.id - t2.id = 1 and t2.id - t1.id = 1 and t3.id - t1.id = 2)
-- t3, t2, t1

将上面的条件加入SQL：


select t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
    (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) 
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1)
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) 
)
;
pic13.png

结果里包含了重复列，因此还需要用DISTINCT来去重。

MYSQL

select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
    (t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) 
    or
    (t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) 
    or
    (t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2)
)
order by t1.id
;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 学习笔记:MySQL入门经典(机械工业出版社)
    观其大纲 page 01 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 M...
    周少言阅读 3,175评论 0赞 33
  • MYSQL基础
    MYSQL 基础知识 1 MySQL数据库概要 2 简单MySQL环境 3 数据的存储和获取 4 MySQL基本操...
    Kingtester阅读 7,857评论 5赞 116
  • 数据库性能优化详解
    转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,824评论 0赞 44
  • MySQL简介和安装
    1. 了解SQL 1.1 数据库基础 ​ 学习到目前这个阶段，我们就需要以某种方式与数据库打交道。在深入学习MyS...
    锋享前端阅读 1,139评论 0赞 1
  • 小红
    她叫小红，从小就是优，清秀的脸蛋有着并不夸张的美丽...... 她从小就在寨子里长大，那里山清水秀，如诗如画，放学...
    机汤掌勺人阅读 295评论 0赞 1