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
;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,362评论 5赞 477
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,330评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,247评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,560评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,580评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,569评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,929评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,587评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,840评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,596评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,678评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,366评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,945评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,929评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,165评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 43,271评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,403评论 2赞 342

推荐阅读更多精彩内容

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