力扣数据库刷题(一)

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

表: Employee

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| id           | int     |
| name         | varchar |
| salary       | int     |
| departmentId | int     |
+--------------+---------+
id 是该表的主键列(具有唯一值的列)。
departmentId 是 Department 表中 ID 的外键（reference 列）。
该表的每一行都表示员工的ID、姓名和工资。它还包含了他们部门的ID。

表: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| name        | varchar |
+-------------+---------+
id 是该表的主键列(具有唯一值的列)。
该表的每一行表示部门ID和部门名。

公司的主管们感兴趣的是公司每个部门中谁赚的钱最多。一个部门的 高收入者 是指一个员工的工资在该部门的 不同 工资中 排名前三 。

编写解决方案，找出每个部门中 收入高的员工 。

以 任意顺序 返回结果表。

返回结果格式如下所示。

输入: 
Employee 表:
+----+-------+--------+--------------+
| id | name  | salary | departmentId |
+----+-------+--------+--------------+
| 1  | Joe   | 85000  | 1            |
| 2  | Henry | 80000  | 2            |
| 3  | Sam   | 60000  | 2            |
| 4  | Max   | 90000  | 1            |
| 5  | Janet | 69000  | 1            |
| 6  | Randy | 85000  | 1            |
| 7  | Will  | 70000  | 1            |
+----+-------+--------+--------------+
Department  表:
+----+-------+
| id | name  |
+----+-------+
| 1  | IT    |
| 2  | Sales |
+----+-------+
输出: 
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      | 90000  |
| IT         | Joe      | 85000  |
| IT         | Randy    | 85000  |
| IT         | Will     | 70000  |
| Sales      | Henry    | 80000  |
| Sales      | Sam      | 60000  |
+------------+----------+--------+
解释:
在IT部门:
- Max的工资最高
- 兰迪和乔都赚取第二高的独特的薪水
- 威尔的薪水是第三高的

在销售部:
- 亨利的工资最高
- 山姆的薪水第二高
- 没有第三高的工资，因为只有两名员工

解题思路

算法：公司里前 3 高的薪水意味着有不超过 3 个工资比这些值大

select e1.name,e1.salary from Employee e1
where 3>
(
    select count(distinct e2.salary) from Employee e2
    where e2.salary > e1.salary
)

与部门表拼接

完整题解

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
        )
;

该方法比较好理解，适合入门新手锻炼思路，下面是使用SQL的高级窗口函数DENSE_RANK()

DENSE_RANK()将具有相同值的行分配相同的排名，不会跳过排名，然后通过共享相同值的行数递增排序。

一般的语法如下：

DENSE_RANK() OVER (ORDER BY column)

    ORDER BY指定用于对结果集进行排序的列或表达式。

DENSE_RANK() OVER (PARTITION BY column ORDER BY column)

    PARTITION BY是一个可选的子句，用于根据指定的列将结果集划分为多个分区。排序在每个分区内分别应用。

select Department, Employee, Salary
from(
    select d.name as Department, e.name as Employee, e.Salary as Salary,
    dense_rank() over(partition by e.departmentId order by Salary desc) as rk
    from Employee as e join Department as d on e.departmentId =d.id
) t
where rk <= 3

262.行程和用户

表：Trips

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| id          | int      |
| client_id   | int      |
| driver_id   | int      |
| city_id     | int      |
| status      | enum     |
| request_at  | varchar  |     
+-------------+----------+
id 是这张表的主键（具有唯一值的列）。
这张表中存所有出租车的行程信息。每段行程有唯一 id ，其中 client_id 和 driver_id 是 Users 表中 users_id 的外键。
status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

表：Users

+-------------+----------+
| Column Name | Type     |
+-------------+----------+
| users_id    | int      |
| banned      | enum     |
| role        | enum     |
+-------------+----------+
users_id 是这张表的主键（具有唯一值的列）。
这张表中存所有用户，每个用户都有一个唯一的 users_id ，role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

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

编写解决方案找出 "2013-10-01" 至 "2013-10-03" 期间有 至少 一次行程的非禁止用户（乘客和司机都必须未被禁止）的 取消率。非禁止用户即 banned 为 No 的用户，禁止用户即 banned 为 Yes 的用户。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。

返回结果表中的数据 无顺序要求 。

解题思路

首先从users表下手找到未禁止的用户，这个时候 可以用两种写法 一个是 not in  禁止用户  

WHERE 
T.Client_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)

或者是  in 禁止用户

where client_id in
(select users_id from Users
where banned = 'No') 
and driver_id in
(select users_id from Users
where banned = 'No')

取消率的计算  使用sum函数结合if函数

sum(
    if(t.status = 'completed',0,1)
) / count(t.status)

完整题解

select t.request_at as 'Day',       
round(
    sum(if(t.status = 'completed',0,1)) / count(t.status),2
) as 'Cancellation Rate'
from  trips AS t
where client_id in
    (select users_id from Users
     where banned = 'No') 
and driver_id in
    (select users_id from Users
    where banned = 'No')
AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY t.request_at 

601.体育馆人流量

表：Stadium

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是该表中具有唯一值的列。
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写解决方案找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列 的结果表。

解题思路

1.针对连续性问题使用Row_number()函数

该函数为结果集的分区中的每一行分配一个连续的整数

ROW_NUMBER() OVER ( 
    [PARTITION BY ,[{,}…]]   --分区
    [ORDER BY [ASC|DESC],[{,}…]]  --排序 
)

2.使用with  as 语法创建临时表

with as 语句是SQL中的一种常用语法，它可以为一个查询结果或子查询结果创建一个临时表，并且可以在后续的查询中使用这个临时表，在查询结束后该临时表就被清除了。这种语法的使用可以使得复杂的查询变得简单，同时也可以提高查询效率

with t1 as(
    select *,id - row_number() over(order by id) as rk
    from stadium
    where people >= 100
)

输出结果为

| id | visit_date | people | rk |
| -- | ---------- | ------ | -- |
| 2  | 2017-01-02 | 109    | 1  |   --2-1
| 3  | 2017-01-03 | 150    | 1  |   --3-2
| 5  | 2017-01-05 | 145    | 2  |   --5-3
| 6  | 2017-01-06 | 1455   | 2  |   --6-4
| 7  | 2017-01-07 | 199    | 2  |   --7-5
| 8  | 2017-01-09 | 188    | 2  |   --8-6

当id连续时应该输出的rk列数字是相同的，实现了对连续id的分组

完整题解

with t1 as(
    select *,id - row_number() over(order by id) as rk
    from stadium
    where people >= 100
)


select id,visit_date,people from t1
where rk in(
    select rk
    from t1
    group by rk
    having count(rk) >= 3
)