Erlo

SQL练习——LeetCode解题和总结

时间:2020-11-17   阅读:12次   来源:博客园
页面报错
点赞

只用于个人的学习和总结。

178. Rank Scores

 一、表信息

 二、题目信息

对上表中的成绩由高到低排序,并列出排名。当两个人获得相同分数时,取并列名次,且名词中无断档。

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes" between ranks.

For example, given the above Scores table, your query should generate the following report (order by highest score):

 三、参考SQL

(1)方法一:直接表内连接

1 select s1.Score,count(distinct s2.Score) as 'Rank'
2 from Scores s1
3 inner join Scores s2
4 on s1.Score<=s2.Score
5 group by s1.id
6 order by count(distinct s2.Score);

分组字段和查询字段不一致,可以在嵌套一层select。

解题思路:

1、欲得到排名,肯定用count进行统计,一个表肯定不行;

2、连接条件:得到大于或等于某个数的集合,比如大于等于3.50的集合就是{3.50,3.65,4.00,3.85,4.00,3.65}

3、分组:得到大于或等于某个数的6个集合组

4、去重统计:因为是排名无断档,需要进行去重再统计,不然就变成统计集合的个数(即大于等于某个值的个数),而不是该值在集合中排名

(2)方法二:窗口函数——dense_rank()(MySQL8.0)

1 SELECT  Score,
2  DENSE_RANK() OVER(ORDER BY Score DESC) AS 'Rank'
3 FROM Scores;

窗口函数复习:https://zhuanlan.zhihu.com/p/135119865

180. Consecutive Numbers

一、表信息

 二、题目信息

找出连续出现三次及以上的数字。例如,上表中,应该返回数字 1。

Write a SQL query to find all numbers that appear at least three times consecutively. For example, given the above Logs table, 1 is the only number that appears consecutively for at least three times.

 

 

 

三、参考SQL

方法一:多次连接

select distinct a.num as ConsecutiveNums
from Logs a
inner join Logs b on a.id=b.id+1
inner join Logs c on a.id=c.id+2
where a.num=b.num and a.num=c.num;

思路总结:

1.连续三次出现,意味着ID连续、值相等。

 2.多次连接时,让当前记录、下条记录、下下条记录拼接在一起

3.筛选值相等的行记录,有可能连续出现大于3次,去重即可得到该num。

方法二:窗口函数——行向下偏移lead()

select distinct Num as ConsecutiveNums
from
    (select Num,
    lead(num,1) over(order by id) as next_num,
    lead(num,2) over(order by id) as next_next_num
    from Logs) t
where t.Num=t.next_num
and t.Num=t.next_next_num;

窗口函数lead():https://www.begtut.com/mysql/mysql-lead-function.html

181. Employees Earning More Than Their Managers[e]

一、表信息

如下 Employee 表中包含全部的员工以及其对应的经理。

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

 二、题目信息

基于如上 Employee 表,查出薪水比其经理薪水高的员工姓名。

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.

 三、参考SQL

自连接:

1 select e1.Name as Employee
2 from Employee e1
3 inner join Employee e2
4 on e1.ManagerId=e2.Id
5 where e1.Salary>e2.Salary;

182. Duplicate Emails[e]

一、表信息

二、题目信息

查询重复的邮箱

Write a SQL query to find all duplicate emails in a table named Person. For example, your query should return the following for the above table:

三、参考SQL

方法一:自己写的

select Email
from Person
group by Email
having count(*)>1;

方法二:官方答案

1 SELECT Email FROM
2  (SELECT Email, COUNT(id) AS num
3   FROM Person
4   GROUP BY Email) AS tmp
5 WHERE num > 1;

 

183. Customers Who Never Order[e]

一、表信息

假设一个网站上包含如下两张表:顾客表和订单表

Suppose that a website contains two tables, the Customers table and the Orders table.

表一:Customers 

表二:Orders 

二、题目信息

找出没有下过订单的顾客姓名。

Write a SQL query to find all customers who never order anything. Using the above tables as an example, return the following:

三、参考SQL

方法一:左外连接

1 select Name as Customers
2 from Customers c
3 left join Orders o
4 on c.Id=o.CustomerId 
5 where o.Id is null;

方法二:子查询(官方方法)

select Name as Customers
from Customers
where Id not in(
    select CustomerId from Orders
);

184. Department Highest Salary[M]

一、表信息

表一:Employee

 

 表二:Department

二、题目信息

查询每个部门中,薪水最高的员工姓名及其薪水。

Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).

 

三、参考SQL

方法一:窗口函数——dense_rank()

select d.Name as Department,t.Name as Employee,t.Salary
from
    (
    select *,
    dense_rank() over(partition by DepartmentId order by Salary DESC) as ranking
    from Employee
    ) t
inner join Department d
on t.DepartmentId=d.Id
where ranking=1;

同一层select下,字段别名不用能与条件筛选!!!执行顺序问题from——.....——where——.....——select——.....

思路:

1.用dense_rank()不断档的方式,给各个部门分组的工资大小排名

2.取排名为1的都是最大工资

方法二:关联子查询

 1 select 
 2     d.Name as Department,
 3     t.Name as Employee,
 4     t.Salary
 5 from Department d
 6 inner join
 7     (select Name,DepartmentId,Salary
 8      from Employee e
 9      where (e.DepartmentId,Salary) in
10         (select DepartmentId,max(Salary)
11         from Employee
12         group by DepartmentId)
13     ) t
14 on d.Id=t.DepartmentId;

in可以进行多属性值(column1_name, column2_name,....)进行筛选,一一对应所筛选的字段。

思路:

1.找出部门中最大的工资

2.让原始表中各部门的工资等于最大工资,罗列出所有最大工资。

3.内连接查询相关信息

185. Department Top Three Salaries[h]

经典topN问题:记录每组最大的N条记录,既要分组,又要排序。

一、表信息

表一:Employee

 表二:Department

二、题目信息

查询各部门薪水排名前三名的员工姓名及薪水。

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 (order of rows does not matter).

三、参考SQL

方法一:窗口函数——dense_rank()

 1 select 
 2     d.name as Department,
 3     e.name as Employee,
 4     Salary
 5 from Department d
 6 inner join 
 7 (
 8     select name,Salary,DepartmentId,
 9     dense_rank() over(partition by DepartmentId order by Salary desc) as ranking
10     from Employee
11 ) e
12 on d.Id=e.DepartmentId
13 where ranking<=3;

思路:

1.用dense_rank(),按照部门分组并降序排列,不间断编上排名

2.筛选排名小于等于3的记录,就是前三工资的记录。

(ps:用窗口函数做,思路和上题差不多,区别只是后面筛选的条数)

方法二:自连接分组筛选

 1 select 
 2     d.name as Department,
 3     e.name as Employee,
 4     Salary
 5 from Department d
 6 inner join
 7 (
 8     select e1.Id,e1.DepartmentId,e1.name,e1.Salary
 9     from Employee e1
10     inner join Employee e2
11     on e1.DepartmentId=e2.DepartmentId
12     and e1.Salary<=e2.Salary
13     group by e1.Id
14     having count(distinct e2.Salary)<=3
15 ) e
16 on d.Id=e.DepartmentId;

思路:

1.关键是要找出各部门前三工资的记录:

  自连接,连接条件为部门相等,工资比我大或者相等;

  按员工分组,则组记录为比我大或者相等全部员工记录;

  统计组记录条数,少于等于3条,则表示我一定是工资第三的,这里有一点注意,不能用count(*),因为和我工资相等的员工除了我本身,还有可能有其他员工,如果不去重,就会导致记录条数大于3(假设我刚好是第三),从而筛选掉,这不是想要的结果;

2.再按需求做相关查询即可

(ps这题的自连接条件思路和178题差不多)

196. Delete Duplicate Emails[E]

一、表信息

二、题目信息

删除邮件重复的行,当有重复值时,保留Id最小的行。

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id. For example, after running your query, the above Person table should have the following rows:

三、参考SQL

方法一:子查询

1 delete from Person
2 where Id not in(
3     select Id from
4     (
5         select min(Id) AS Id
6         from Person
7         group by Email
8     ) t
9 );

思路:

1.子查询找出不用删除的邮箱Id集合(重复邮箱的最小Id加上邮箱不重复的Id):邮箱分组,取最小Id即可。

2.删除时,判断Id不在此集合即可

(ps:MySQL不让同时对统一表进行修改和查询操作,所以需要外层嵌套一层辅助表;min(Id)要记得起别名)

方法二:自连接

1 delete p1 from Person p1
2 inner join Person p2
3 on p1.Email=p2.Email
4 and p1.Id>p2.Id;

思路:

1.邮箱相等进行连接得到的集合为:(1)邮箱相等,Id也相等。即不重复的(2)邮箱相等,p1.Id>p2.Id(3)邮箱相等,p1.Id<p2.Id

2.把邮箱相等,p1.Id>p2.Id提取出来,删除端即可。这样就保留了小和不重复的。

(ps:联级删除也有这种语法——delete 表名 from .....)

197. Rising Temperature[E]

一、表信息

二、题目信息

以下图为例,找出比前一天温度高的id。

Write an SQL query to find all dates' id with higher temperature compared to its previous dates (yesterday).

Return the result table in any order.

The query result format is in the following example:

            

三、参考SQL

1 select w1.id as Id from Weather w1
2 inner join Weather w2
3 on datediff(w1.recordDate,w2.recordDate)=1
4 where w1.temperature>w2.temperature;

思路:

1.自内连的笛卡尔积中,去取出间隔相差一天的记录,用datadiff()函数。

2.再筛选出温度比上一天高ID即可。

(ps;日期不能进行简单的相加相减,最好使用日期函数。https://www.w3school.com.cn/sql/sql_dates.asp

复习日期函数:https://leetcode-cn.com/problems/rising-temperature/solution/tu-jie-sqlmian-shi-ti-ru-he-bi-jiao-ri-qi-shu-ju-b/

262. Trips and Users[H]

一、表信息

表一:Trips

该表包含全部出租车信息的记录。每一条记录有一个 Id,ClientId 和 Drive_Id 都是与 Users 表联结的外键。Status 包含 completed, cancelled_by_driver, 和 cancelled_by_client 三种状态。

The Trips table holds all taxi trips. Each trip has a unique Id, while Client_Id and Driver_Id are both foreign keys to the Users_Id at the Users table. Status is an ENUM type of (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’).

 

 

 表二:Users

该表包含全部的用户信息。每一个用户都有一个 Id,Role有三种状态:client, driver 以及 partner。

The Users table holds all users. Each user has an unique Users_Id, and Role is an ENUM type of (‘client’, ‘driver’, ‘partner’).

二、题目信息

找出2013年10月1日至2013年10月3日期间,每一天 未被禁止的 (unbanned) 用户的订单取消率。

Write a SQL query to find the cancellation rate of requests made by unbanned users (both client and driver must be unbanned) between Oct 1, 2013 and Oct 3, 2013. The cancellation rate is computed by dividing the number of canceled (by client or driver) requests made by unbanned users by the total number of requests made by unbanned users.

取消率的计算方式如下:(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)

For the above tables, your SQL query should return the following rows with the cancellation rate being rounded to two decimal places.

三、参考SQL

方法一:子查询筛选出有效订单记录

SELECT
    Request_at AS 'Day',
    round( count( CASE t_Status WHEN 'completed' THEN NULL ELSE 1 END ) / count( * ), 2 ) AS 'Cancellation Rate' 
FROM
    (
SELECT
    Request_at,
    Status as t_Status
FROM
    Trips 
WHERE
    Client_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes' ) 
    AND Driver_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes' ) 
    ) t 
WHERE
    Request_at BETWEEN '2013-10-01' 
    AND '2013-10-03' 
GROUP BY
    Request_at

思路:

1.重要一点是筛选出有效订单记录集合:顾客和司机都是未被禁止的!

子查询:

  Client_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned ='Yes' ) 

  AND Driver_Id NOT IN ( SELECT Users_Id FROM Users WHERE Banned = 'Yes')

2.在上一步基础上,统计每天分组被取消的订单,用case when语句:当订单是complated完成状态时,返回null,这样count就不会计数。

方法二:连接查询筛选出有效订单记录集合

https://leetcode-cn.com/problems/trips-and-users/solution/san-chong-jie-fa-cong-nan-dao-yi-zong-you-gua-he-n/

计算订单取消率还可以用avg(Status!='completed'):

https://leetcode-cn.com/problems/trips-and-users/solution/ci-ti-bu-nan-wei-fu-za-er-by-luanz/

511. Game Play Analysis I[E]

一、表信息

Activity表记录了游戏用户的行为信息,主键为(player_id, event_date)的组合。每一行记录每个游戏用户登录情况以及玩的游戏数(玩的游戏可能是0)。

(player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.

二、题目信息

查询每个用户首次登陆的日期

Write an SQL query that reports the first login date for each player.

The query result format is in the following example:

三、参考SQL

1 SELECT
2     player_id,
3     MIN( event_date ) AS first_login 
4 FROM
5     Activity 
6 GROUP BY
7     play_id 
8 ORDER BY
9     play_id;

512. Game Play Analysis II[E]

一、表信息

同上题

二、题目信息

相关推荐

评论留言

还没有评论留言,赶紧来抢楼吧~~

吐槽小黑屋()

* 这里是“吐槽小黑屋”,所有人可看,只保留当天信息。

  • Erlo吐槽

    Erlo.vip2021-01-22 14:23:48Hello、欢迎使用吐槽小黑屋,这就是个吐槽的地方。
  • 返回顶部

    给这篇文章打个标签吧~

    棒极了 糟糕透顶 好文章 PHP JAVA JS 小程序 Python SEO MySql 确认