LeetCode-SQL(三）

leetcode 系列

• LeetCode-算法（一）
• LeetCode-SQL(一）
• LeetCode-SQL(二）
• LeetCode-SQL(三）

---

262. 行程和用户

https://leetcode-cn.com/problems/trips-and-users/

写一段 SQL 语句查出 “2013-10-01” 至 “2013-10-03” 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。

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

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数 。



解题：
1、限定日期
2、获取每个client和driver的状态
3、求每个行程的状态
4、根据日期group by聚合得到结果

SELECT A.Request_at as Day,
       CONVERT(format(sum(A.is_completed) / count(*), 2), decimal(10,2) ) "Cancellation Rate"
FROM (
         SELECT t.Request_at,
                t.status,
                case when  t.status <> "completed" then 1 else 0 end is_completed ,
                (SELECT banned FROM users WHERE users_id = t.client_id) client_status,
                (SELECT banned FROM users WHERE users_id = t.Driver_Id) driver_status
         FROM Trips t
         WHERE t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
     ) A
WHERE A.client_status = "No"
  AND A.driver_status = "No"
GROUP BY A.Request_at；

579. 查询员工的累计薪水🔒

https://leetcode-cn.com/problems/find-cumulative-salary-of-an-employee/



方法一：先求最近 2 个月薪资
想法
分两步解决这个问题。第一步是求得一个员工最近 3 个月的累积薪资，然后把最近一个月薪资从包含它的数据中去除。

如果你觉得求一个员工 3 个月的累积薪资有难度，可以从 2 个月的累积薪资开始考虑。通过把 Employee 表与它自己做一个连接，你可以得到每个员工 2 个月的薪资表。

SELECT *
FROM
    Employee E1
        LEFT JOIN
    Employee E2 ON (E2.id = E1.id
        AND E2.month = E1.month - 1)
ORDER BY E1.id ASC , E1. month DESC
Id	Month	Salary	Id	Month	Salary

SELECT
    E1.id,
    E1.month,
    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0)) AS Salary
FROM
    Employee E1
        LEFT JOIN
    Employee E2 ON (E2.id = E1.id
        AND E2.month = E1.month - 1)
ORDER BY E1.id ASC , E1.month DESC；

id	month	Salary
1	4	100
1	3	70
1	2	50
1	1	20
2	2	50
2	1	20
3	4	130
3	3	100
3	2	40

类似的，你可以将此表再次连接来求得 3 个月的累计薪资。

SELECT
    E1.id,
    E1.month,
    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary
FROM
    Employee E1
        LEFT JOIN
    Employee E2 ON (E2.id = E1.id
        AND E2.month = E1.month - 1)
        LEFT JOIN
    Employee E3 ON (E3.id = E1.id
        AND E3.month = E1.month - 2)
ORDER BY E1.id ASC , E1.month DESC;

id	month	Salary
1	4	130
1	3	90
1	2	50
1	1	20
2	2	50
2	1	20
3	4	170
3	3	100
3	2	40

除此以外，我们还需要按要求去除最近一个月薪资的影响。如果我们有一个包含了每个 id 和该员工最近一个月是几月的临时表，我们就可以很轻易地把每个员工最近一个月的工资去除。

id	month
1	4
2	2
3	4

下面是生成此表的代码。

SELECT
    id, MAX(month) AS month
FROM
    Employee
GROUP BY id
HAVING COUNT(*) > 1;

最后，我们把这两张表连接起来得到我们想要的连续 3 个月薪资但不包含最近一个月薪资的结果。

SELECT
    E1.id,
    E1.month,
    (IFNULL(E1.salary, 0) + IFNULL(E2.salary, 0) + IFNULL(E3.salary, 0)) AS Salary
FROM
    (SELECT
        id, MAX(month) AS month
    FROM
        Employee
    GROUP BY id
    HAVING COUNT(*) > 1) AS maxmonth
        LEFT JOIN
    Employee E1 ON (maxmonth.id = E1.id
        AND maxmonth.month > E1.month)
        LEFT JOIN
    Employee E2 ON (E2.id = E1.id
        AND E2.month = E1.month - 1)
        LEFT JOIN
    Employee E3 ON (E3.id = E1.id
        AND E3.month = E1.month - 2)
ORDER BY id ASC , month DESC;

方法二：直接计算所有需要累计的月份
想法

我们可以先用一个查询求得对于每个员工来说需要累计的月份，然后将这些月份对应的前两月的工资相加。

每个员工需要进行累计的月份是除他的最大月份外的所有月份，按员工分组并用 max 聚合函数可以求得每个员工的最大月份。求得最大月份后，再查询对于每个员工来说所有不等于最大月份的月份。这些月份即为需要累计的月份。

select Employee.Id as Id, Employee.Month as Month
from Employee, (select Id, max(Month) as Month
    from Employee
    group by Id) as LastMonth
    where Employee.Id = LastMonth.Id and Employee.Month != LastMonth.Month

然后对于每个需要累计的月份，记录它的前两个月份，将对应关系记录到一个表中，这样的查询可以通过 join 语句实现。

select a.Id as Id, a.Month as AccMonth, b.Month as Month, b.Salary as Salary
from 
(
    select Employee.Id as Id, Employee.Month as Month
    from Employee, (select Id, max(Month) as Month
        from Employee
        group by Id) as LastMonth
        where Employee.Id = LastMonth.Id and Employee.Month != LastMonth.Month) as a 
join Employee as b
on a.Id = b.Id and a.Month - b.Month <= 2 and a.Month - b.Month >= 0

进行上述查询后，对于题目的输入，可以得到这样一张表（为了方便理解，这里对表进行了排序）：

Id	AccMonth	Month	Salary
1	1	1	20
1	2	1	20
1	2	2	30
1	3	1	20
1	3	2	30
1	3	3	40
2	1	1	20
3	2	2	40
3	3	2	40
3	3	3	60

可以看到，AccMonth 对应每个需要输出的累计月份，而 AccMonth 对应的 Month 其前三个月份。所以此时只要按照员工 Id 与需要累计的月份 AccMonth 分组，并用 sum 聚合函数计算累计工资即可。

select Id, AccMonth as Month, sum(Salary) as Salary
from
(
    select a.Id as Id, a.Month as AccMonth, b.Month as Month, b.Salary as Salary
    from 
    (
        select Employee.Id as Id, Employee.Month as Month
        from Employee, (select Id, max(Month) as Month
            from Employee
            group by Id) as LastMonth
            where Employee.Id = LastMonth.Id and Employee.Month != LastMonth.Month) as a 
    join Employee as b
    on a.Id = b.Id and a.Month - b.Month <= 2 and a.Month - b.Month >= 0
) as acc
group by Id, AccMonth
order by Id, Month desc；

603. 连续空余座位🔒

https://leetcode-cn.com/problems/consecutive-available-seats/

方法：使用自连接 join 和 abs() [Accepted]
想法

这个问题中只有一张表，所以我们需要使用自连接 self join 来解决这个相对复杂的问题。

算法

首先，我们看看将这个表自连接后得到什么结果。

注意：将两个表连接的结果是这两个表的 笛卡尔乘积 。

select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b;

为了找到连续空座位，a.seat_id 里面的值应该大于 b.seat_id 且两者都应该为空。

select a.seat_id, a.free, b.seat_id, b.free
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true;

最后，选择上表中的字段 seat_id ，并排序后返回。

注意：你可能发现 seat_id 4 在表中出现了两次。这是因为座位 4 与 3 和 5 都相邻。所以我们需要使用 distinct 将重复记录筛除。

sql

select distinct a.seat_id
from cinema a join cinema b
  on abs(a.seat_id - b.seat_id) = 1
  and a.free = true and b.free = true
order by a.seat_id；

620. 有趣的电影

https://leetcode-cn.com/problems/not-boring-movies/

题解：使用 MOD() 函数
我们可以使用 mod(id,2)=1 来确定奇数 id，然后添加 description != ‘boring’ 来解决问题。

select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC;

1407. 排名靠前的旅行者🔒

https://leetcode-cn.com/problems/top-travellers/

写一段 SQL , 报告每个用户的旅行距离。

返回的结果表单，以 travelled_distance 降序排列 ，如果有两个或者更多的用户旅行了相同的距离, 那么再以 name 升序排列 。

查询结果格式如下例所示。


题解：
从测试用例中可以看出，有用户的出行量是0.这里的问题在于，这样的用户在rides中没有出现，所以我们在使用users作为左边的表来join右边的rides的时候，使用left join，这样所有的用户就能够显示出来了。
光做到上面的还不够，因为得到的是null，而不是0，因此这里要使用coalesce这个函数对null进行处理。
就是这么简单！

select name, coalesce(sum(distance), 0) as travelled_distance
from users left join rides on users.id=rides.user_id
group by name
order by travelled_distance desc, name;