Leetcode刷数据库题

 3,775浏览     wenqyAdmin   

在Leetcode网刷了几个数据库题目，顿感渣渣，有好几题都copy了别人的答案，有点无力啊。做个备忘吧，前事不忘。Leetcode只能提交mysql语法的sql语句。

1.列出工资比经理更高的雇员

Employee表，包含了所有雇员及他们的经理。

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

如上表所示，工资比经理更高的雇员：

+----------+
| Employee |
+----------+
| Joe      |
+----------+

主要是表的自连接，看成雇员表与经理表关联比较

  1. select e.Name as Employee from Employee e,Employee m where e.ManagerId = m.id and e.Salary > m.Salary

写成join on格式

  1. select e1.Name from Employee e1 join Employee e2 on e1.ManagerId = e2.Id and e1.Salary>e2.Salary

2.找出重复的电子邮件

Person表数据如下：

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

如上表，所给示例答案：

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

一种方法就是按email分组，并查找总数大于1的email

  1. select email from Person group by email having count(email) > 1

还可以自连接，查找email相同但不同主键的email

  1. SELECT distinct p1.Email from Person p1 INNER JOIN Person p2 ON p1.Email = p2.Email WHERE p1.Id <> p2.Id;

3.删除重复的电子邮件

Person表，数据如下：

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+

保留Id最小，电子邮件唯一的数据，示例：

+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+

采用自连接，可以删除email相同但Id更大的数据

  1. delete a from Person a,Person b where a.email = b.email and a.id > b.id

当然，也可以先查出所有不同email中的最小Id，然后删除剩下的数据

  1. delete from Person where Id not in
  2. (select Id
  3.      from
  4.       (select min(Id) as Id
  5.           from Person
  6.           group by Email
  7.        ) p
  8.  );

4.查询没有下过订单的客户

Customers表

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Orders表

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

示例答案：

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

主要判断一个表的数据在不在另一个表里，可以写成很多：

  1. select c.Name from Customers c where c.Id not in (select customerId from Orders)
  1. select c.Name from Customers c where (select count(*) from Orders o where o.customerId=c.id)=0
  1. select c.Name from Customers c where not exists (select * from Orders o where o.customerId=c.id)
  1. select c.Name as Customers from Customers c left join Orders o on c.Id=o.CustomerId where o.CustomerId is null

5.查询所有气温比前一天高的日期id

Weather表

+---------+------------+------------------+
| Id(INT) | Date(DATE) | Temperature(INT) |
+---------+------------+------------------+
|       1 | 2015-01-01 |               10 |
|       2 | 2015-01-02 |               25 |
|       3 | 2015-01-03 |               20 |
|       4 | 2015-01-04 |               30 |
+---------+------------+------------------+

示例答案：

+----+
| Id |
+----+
|  2 |
|  4 |
+----+
 也主要采用自连接方法
  1. select today.id from Weather today, Weather yesterday where subdate(today.date,1)=yesterday.date and today.Temperature > yesterday.Temperature

mysql相隔一天的天数比较
datediff(today.date,yesterday.date)= 1  TO_DAYS(t1.Date) = TO_DAYS(t2.Date) + 1

6.查询第二高的工资

Employee表，如果没有第二高则返回null

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

一种方法取是除掉最大值后的最大值

  1. SELECT max(Salary) FROM Employee WHERE Salary < (SELECT max(Salary) FROM Employee)

还可以利用mysql特有的limit语句

  1. select (
  2.   select distinct Salary from Employee order by Salary Desc limit 1 offset 1
  3. )as second

7.查询连续出现3次以上的数

Logs 表，示例答案：1

+----+-----+
| Id | Num |
+----+-----+
| 1  |  1  |
| 2  |  1  |
| 3  |  1  |
| 4  |  2  |
| 5  |  1  |
| 6  |  2  |
| 7  |  2  |
+----+-----+

自连接3个表

  1. select distinct pre.num from Logs pre, Logs cur, Logs nxt where pre.id = cur.id – 1 and pre.id = nxt.id – 2 and pre.num = cur.num and pre.num = nxt.num

采用变量的方式

  1. select distinct r.num  from
  2.     (select num,
  3.         case when @last = num then @count:=@count+1
  4.             when @last<>@last:=num then @count:=1
  5.             end as n
  6.         from Logs
  7.     ) r ,(select @count:=0,@last:=(select num from Logs limit 0,1)) temp where r.n>=3

8.成绩分等级

Scores表

+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

示例答案：

+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

排名可以看做排序后排在当前分数前面的个数

  1. SELECT a.score,(select count(distinct score) from Scores  b where  a.score<= b.score) FROM Scores  a order by score desc

利用group by实现

  1. SELECT T2.Score Score, (SELECT COUNT(*) + 1 FROM (SELECT T1.Score FROM Scores T1 GROUP BY Score ORDER BY Score DESC) TEMP WHERE T2.Score < TEMP.Score) Rank FROM Scores T2 ORDER BY Score DESC;

9.列出各个部门的最高工资

Employee表

+----+-------+--------+--------------+
| Id | Name  | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 70000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
+----+-------+--------+--------------+

Department表

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

利用group by 和 max函数

  1. select b.Name,a.Name,a.Salary from Employee a , Department b, (select max(Salary) Salary,DepartmentId from Employee group by DepartmentId) c where a.DepartmentId = b.id and c.DepartmentId = a.DepartmentId and a.Salary = c.Salary

其他实现

  1. select b.Name Department, a.Name Employee, a.Salary from
  2. (
  3.     select a.Name, a.Salary, a.DepartmentId
  4.     from Employee a left outer join Employee b
  5.     on a.DepartmentId = b.DepartmentId
  6.     and a.Salary < b.Salary
  7.     where b.Id is null
  8. ) a join Department b
  9. on a.DepartmentId = b.Id;
  1. SELECT dep.Name as Department, emp.Name as Employee, emp.Salary from Department dep, Employee emp
  2. where emp.DepartmentId=dep.Id and emp.Salary=(Select max(Salary) from Employee e2 where e2.DepartmentId=dep.Id)

10.列出各个部门的前三高工资

Employee表

+----+-------+--------+--------------+
| 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    |
+----+----------+

示例答案：

+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Randy    | 85000  |
| IT         | Joe      | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
用个数比较
  1. Select dep.Name as Department, emp.Name as Employee, emp.Salary
  2. from Department dep, Employee emp where emp.DepartmentId=dep.Id and
  3. (Select count(distinct Salary) From Employee where DepartmentId=dep.Id and Salary>emp.Salary)<3

用复杂的join on语句，不利于扩展

  1. SELECT
  2.     a. NAME AS Department,
  3.     g. NAME AS Employee,
  4.     g.Salary
  5. FROM
  6.     Department a
  7. INNER JOIN Employee g ON a.Id = g.DepartmentId
  8. LEFT JOIN (
  9.     SELECT
  10.         e.DepartmentId,
  11.         max(e.Salary) AS Salary
  12.     FROM
  13.         Employee e
  14.     INNER JOIN (
  15.         SELECT
  16.             c.DepartmentId,
  17.             max(c.Salary) AS Salary
  18.         FROM
  19.             Employee c
  20.         INNER JOIN (
  21.             SELECT
  22.                 DepartmentId,
  23.                 max(Salary) AS Salary
  24.             FROM
  25.                 Employee
  26.             GROUP BY
  27.                 DepartmentId
  28.         ) b ON c.DepartmentId = b.DepartmentId
  29.         WHERE
  30.             c.Salary < b.Salary
  31.         GROUP BY
  32.             c.DepartmentId
  33.     ) d ON e.DepartmentId = d.DepartmentId
  34.     WHERE
  35.         e.Salary < d.Salary
  36.     GROUP BY
  37.         e.DepartmentId
  38. ) f ON g.DepartmentId = f.DepartmentId
  39. WHERE
  40.     f.DepartmentId IS NULL
  41. OR g.Salary >= f.Salary

11.查询每日订单取消率

Trips表

+----+-----------+-----------+---------+--------------------+----------+
| Id | Client_Id | Driver_Id | City_Id |        Status      |Request_at|
+----+-----------+-----------+---------+--------------------+----------+
| 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|
+----+-----------+-----------+---------+--------------------+----------+

Users表

+----------+--------+--------+
| 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 |
+----------+--------+--------+

按日期分组，主要算出每日的订单取消数

  1. SELECT Request_at as Day,ROUND(COUNT(IF(Status != ‘completed’, TRUE, NULL)) / COUNT(*), 2) AS ‘Cancellation Rate’
  2. FROM Trips
  3. WHERE (Request_at BETWEEN ‘2013-10-01’ AND ‘2013-10-03’)
  4.       AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = ‘Yes’)
  5. GROUP BY Request_at

类似实现

  1. select a.Request_at as Day, round( (select count(*) from Trips as b left join Users as c on b.Client_Id = c.Users_Id where (b.Status = ‘cancelled_by_client’ or b.Status =’cancelled_by_driver’) and c.Banned = ‘No‘ and b.Request_at = a.Request_at)/count(a.Status),2) as Cancellation_Ratefrom Trips as a left join Users as don a.Client_Id = d.Users_Id where d.Banned = ‘No‘ and a.Request_at >= date(“2013-10-01”) and a.Request_at <= date(“2013-10-03”) group by 1order by 1;

12.写个函数，返回第N高工资

Employee表，如果没有则返回null

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

利用limit语句

  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3.   RETURN (
  4.       select  IF(count(*) >= N, Min(rank.Salary), NULL) salary
  5.         from (select distinct salary
  6.               from Employee
  7.               order by salary desc
  8.               limit N
  9.          ) rank
  10.   );
  11. END
  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3.   RETURN (
  4.       SELECT e1.Salary
  5.       FROM (SELECT DISTINCT Salary FROM Employee) e1
  6.       WHERE (SELECT COUNT(*) FROM (SELECT DISTINCT Salary FROM Employee) e2 WHERE e2.Salary > e1.Salary) = N – 1  LIMIT 1
  8.   );
  9. END
  1. CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
  2. BEGIN
  3.   RETURN (
  4.       select  IF(count(*) >= N, Min(rank.Salary), NULL) salary
  5.         from (select distinct salary
  6.               from Employee
  7.               order by salary desc
  8.               limit N
  9.          ) rank
  10.   );
  11. END

总之，要写出一个华丽的SQL也不是一件容易的事，要尽可能少的查库，还要使查询的性能达到最优，如何优化SQL语句等等这些都是值得学习和深思的地方。

小二，再来一碟

赏

发表评论

电子邮件地址不会被公开。 必填项已用*标注

20 + 25 = ?